Formula to Convert Irregular Julian Date to Standard Date

[ewicatcher]

Hello,

I’m trying to convert an irregular Julian date format to a standard date in Excel.

The dates I’m working with are expressed in five (5) digits. The first three numbers in the sequence are the month and day of the year, and the last two digits represent the year. Below are a few examples that I calculated myself:


Sample Julian Date......................Desired Calculation.............................Explanation

33720...................................................December 3, 2020...................................(337th day of 2020)

15214...................................................June 1, 2014..............................................(152nd day of 2014)

04119..................................................February 10, 2019.....................................(41st day of 2019)

Is there a formula I can insert in an adjoining cell to calculate this automatically? I also wonder if the cell with the five-digit Julian date would just be formatted as a number?

I appreciate any assistance you can offer.



Shawn

 

[jtakw]

Hi,

I think your 1st sample is off by 1 day, format result cells to display as you like:

Book3.xlsx
	A	B
1	Julian Date	Converted
2	33720	12/2/2020
3	15214	6/1/2014
4	04119	2/10/2019
Sheet1031

Cell Formulas
Range	Formula
B2:B4	B2	=("1/1/"&RIGHT(A2,2))+LEFT(A2,3)-1

 

[ewicatcher]

Hi,

I think your 1st sample is off by 1 day, format result cells to display as you like:

Book3.xlsx
	A	B
1	Julian Date	Converted
2	33720	12/2/2020
3	15214	6/1/2014
4	04119	2/10/2019
Sheet1031

Cell Formulas
Range	Formula
B2:B4	B2	=("1/1/"&RIGHT(A2,2))+LEFT(A2,3)-1

Your formula worked perfectly. Thank you so much for your help!

 

[jtakw]

You're welcome.