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

Often, food manufacturers, pharmaceutical companies, and different other sectors use the Julian Date format in their products. But this date format is not practical these days. People find it hard to understand as they are used to Gregorian Calendar format only. In this article, we’ll show you the easiest ways to Convert 7 Digit Julian Date to Calendar Date in Excel.

To illustrate, we’re going to use a sample dataset as an example. For instance, the following dataset represents the Product, Dispatch Date in JLD (Julian Date) format of a company.

convert 7 digit julian date to calendar date in excel


Download Practice Workbook

Download the following workbook to practice by yourself.


Introduction to 7 Digit Julian Date Format

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.


3 Ways to Convert 7 Digit Julian Date to Calendar Date in Excel

1. Convert 7 Digit Julian Date to Calendar Date with Combination of DATE, LEFT & RIGHT Functions in Excel

Excel provides many functions and we use them for performing numerous operations. In this method, we’ll apply the DATE, LEFT & RIGHT functions. The DATE function generates a Gregorian calendar date. The arguments for the function include Year, Month, and Day respectively. The LEFT function generates the specified number of characters from the start while The RIGHT function generates the specified number of characters from the end of a string. Therefore, follow the steps given below to Convert Julian Date to Calendar Date in Excel.

STEPS:

  • First, select cell D5 and type the formula:
=DATE(LEFT(C5,4),1,RIGHT(C5,3))
  • Then, press Enter.

Convert 7 Digit Julian Date to Calendar Date with Combination of DATE, LEFT & RIGHT Functions in Excel

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

  • Finally, use the AutoFill tool to fill the series.

Read More: How to Convert Date to Day in Excel (7 Quick Ways)


2. Combine Excel DATE, MOD & INT Functions to Convert 7 Digit Julian Date to Calendar Date

Additionally, we can create a formula with the DATE, MOD & INT functions for converting the Julian Date. We use the MOD function to generate the remainder when a divisor divides a number. The INT function rounds a number to generate the nearest integer value. So, learn the below process to perform the task.

STEPS:

  • Firstly, Select cell D5. Type the formula:
=DATE(INT(C5/10^3),1,MOD(C5,INT(C5/10^3)))
  • Subsequently, press Enter.

Combine Excel DATE, MOD & INT Functions to Convert 7 Digit Julian Date to Calendar Date

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. And the MOD function generates the Remainder when C5 is again divided by that Nearest Integer value.

  • Lastly, complete the rest with the AutoFill.

Read More: How to Convert Date to Month in Excel (6 Easy Methods)


Similar Readings:


3. Apply VBA for Converting 7 Digit Julian Date to Calendar Date in Excel

Moreover, we can use a VBA code to perform the conversion. Hence, follow the process given below to Convert Julian Date to Calendar Date.

STEPS:

  • Select Visual Basic from the Developer tab at first.

Apply VBA for Converting 7 Digit Julian Date to Calendar Date in Excel

  • As a result, the Visual Basic window will pop out.
  • Now, select the Module under the Insert tab.

Apply VBA for Converting 7 Digit Julian Date to Calendar Date in Excel

  • Consequently, the Module window will pop out.
  • Afterward, 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

Apply VBA for Converting 7 Digit Julian Date to Calendar Date in Excel

  • Then, close the Visual Basic window.
  • Next, select cell D5. Here, type the formula:
=JLDtoCD(C5)

  • After that, press Enter.
  • At last, apply the AutoFill tool to convert the rest.

Read More: Excel VBA: First Day of Month (3 Methods)


Conclusion

Henceforth, you will be able to Convert 7 Digit Julian Date to Calendar Date in Excel with the above-described methods. Keep using them and let us know if you have any more ways to do the task. Don’t forget to drop comments, suggestions, or queries if you have any in the comment section below.


Related Articles

Aung

Aung

I'm Aung. Recently I earned my B.Sc. Degree in Electrical and Electronic Engineering. From now on, I will be working in Microsoft Excel and other useful software, and I’ll upload articles related to them. My current goal is to write technical contents for anybody and everybody that will make the learning process of new software and features a happy journey.

1 Comment
  1. Brilliant job.
    Thanks a million..

Leave a reply

ExcelDemy
Logo