Hello Excellers and welcome back to another blog post in my Excel tips #Excel #FormulaFriday blog post in my Excel series. A lot of systems (mainframe usually) use Julian dates. This blogpost will provide three equally useful Julian date converters. Two will be Excel formulas, and the final will be a VBA macro. Let’s get started.
What Is A Julian Date?.
In the context of this blog post, a Julian date refers to a date format that is a combination of the current year and the number of days since the beginning of the year. Please note – there is also a Julian date commonly used in astronomy, which is a serial date system starting on January 1, 4713 B.C.E.
So, our Julian date is simply a date format that (normally) includes 5 digits. Lots of different industries have their own variations on Julian dates, but generally, they are made up of five digits. These digits are broken down as follows:
The first two digits represent the year and the last three represent the day of the year. Let’s run through a few examples to get the feel of this format.
So, for example 01 January 1980 is represented by the following: 1980001. Similarly, 31 December 2020 is represented by 2020366. This article will show you three ways to convert your Julian date to a calendar date using two different methods.
The Julian To Calendar Date Conversion Formulas
Formula 1. The RIGHT, LEFT and DATE functions.
The first formula to convert our julian date to calendar date is below.
=DATE(LEFT(B3,4),1,RIGHT(B3,3))
Let’s talk through the formula. The LEFT function returns four characters from the beginning of the Julian date in cell B3. The RIGHT function returns three characters from the end of the value in cell B3. Finally the DATE function converts both of these values to a calendar date format. Job done.
Formula 2. The MOD, INT and DATE functions.
The second function which results in the same date conversion uses the MOD, INT and DATE functions. The whole formula can be seen below.
=DATE(INT(C5/10^3),1,MOD(C5,INT(C5/10^3)))
So, let’s again walk though the formula. The INT function generates the nearest value after the value in B4 is divided by 1000. The MOD function generates the remainder of the value in B4 when it is also dvidided by 1000. Finally, as in the above formula, the DATE function converts both of these values to a calendar date format.
That’s it! You now know how to convert a Julian date into a calendar date. We hope you enjoyed this tutorial and found it helpful.
If you want all of the Formula Friday articles then bookmark the page below
How To Excel At Excel – Formula Friday Blog Posts.
