# How to Convert 7 Digit Julian Date to Calendar Date in Excel (3 Ways)

The date format which uses the combination of a year and the number of days since the start of that year is known as Julian Date format. In the 7 Digits Julian date format, the first 4 digits refer to the year and the last 3 digits are the total number of days from the beginning of that year. Often, food manufacturers, pharmaceutical companies, and different other sectors use the Julian date format in their products.

To demonstrate the easiest ways to convert 7 digit Julian Date to Calendar Date in Excel, we’ll use the following sample dataset containing dates in Julian Date format.

### Method 1 – Combining DATE, LEFT & RIGHT Functions

The DATE function generates a Gregorian calendar date. The arguments for the function are a year, month, and day respectively. The LEFT function returns the specified number of characters from the start of a string, while the RIGHT function returns the specified number of characters from the end of a string.

Steps:

• Select cell D5 and enter the following formula:
`=DATE(LEFT(C5,4),1,RIGHT(C5,3))`
• Press Enter.

The RIGHT function returns 3 characters from the end of the C5 cell value and the LEFT function returns 4 characters from the beginning. The DATE function converts them to calendar date format and returns the Gregorian date.

• Use the AutoFill tool to fill the series with the formula.

### Method 2 – Combining DATE, MOD & INT Functions

The MOD function returns the remainder from a division operation. The INT function rounds a number to generate the nearest integer value.

Steps:

• Select cell D5 and enter the following formula:
`=DATE(INT(C5/10^3),1,MOD(C5,INT(C5/10^3)))`
• Press Enter.

The DATE function converts the arguments into the year, month, and day format. The INT function generates the nearest integer value after C5 is divided by 1000. The MOD function generates the remainder when C5 is again divided by that nearest integer value.

• Complete the rest of the column using AutoFill.

### Method 3 – Using VBA Code

Steps:

• Select Visual Basic from the Developer tab.

The Visual Basic window will pop out.

• Select the Module from the Insert tab.

The Module window will pop up.

• Copy the following code and paste it into the Module window:
``````Function JLDtoCD(JLD As String) As Long
Dim Year As Integer
Dim Day As Integer
Dim CD As Long
Year = CInt(Left(JLD, 4))
Day = CInt(Right(JLD, 3))
CD = DateSerial(Year, 1, Day)
JLDtoCD = CD
End Function``````

• Close the Visual Basic window.
• Select cell D5 and enter the following formula:
`=JLDtoCD(C5)`

• Press Enter.
• Apply the AutoFill tool to convert the rest of the series.

## Related Articles

<< Go Back to Date Format | Number Format | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Aung Shine

Aung Shine completed his bachelor’s in Electrical and Electronics Engineering from Bangladesh University of Engineering and Technology. It has been almost 2 years since he joined SOFTEKO and actively working on the ExcelDemy project. Currently he works as a Team Leader where he guides his team members to create technical content. He has published 150+ articles and reviewed 50+ articles. He has also solved various user problems before. He has interests in Data Analysis, Power Query, Advanced Excel,... Read Full Bio

1. Brilliant job.
Thanks a million..

2. I have a question…Your Formula works fine for converting a 7-digit Julian date to a calendar date. My question is how you modify to use a 4 digit one? 1st digit is the number of the year, and the next 3 digits are the day of the year – meaning that “3177” would be June 26th, 2023, and “5005” would be January 5th, 2025.
Where I work, we use 16-digit transaction codes and the digits 5-8 represent the Julian date. Extracting the Julian from the transaction number in excel is easy, I just need a formula to convert those 4 digits to a calendar date please!

• Dear PHILIP SMITH,
Thanks for reaching us. I understand that you want to convert a 4-digit Julian date to a Calendar date. In your specified format, the 1st digit is the number of the year, and the next 3 digits are the day of the year.
To demonstrate this problem, I have taken a dataset that contains 4-digit Julian Dates in the range C5:C10. To convert these Julian dates into Calendar dates, I applied the following formula:

=DATE(INT(YEAR(TODAY())/10)*10+VALUE(LEFT(C5,1)), 1, MOD(C5, 1000))

Now, you can extract the Julian date from the transaction number and apply the formula above to convert the Julian date to the calendar date.

Hopefully, I was able to resolve your problem. Let us know your feedback.

Regards,
Seemanto Saha
ExcelDemy

Advanced Excel Exercises with Solutions PDF