Tips & Tricks 139 – Convert from Julian Dates to Excel (Gregorian) Dates

In Tips and Tricks 138, we covered conversion from Excel Dates to Julian Dates. Here, we want to look at reverse.

For 7 Digits Julian Dates, following formula should be used

=DATE(LEFT(A1,4),1,RIGHT(A1,3))

For 5 Digits Julian Dates, following formula should be used depending upon which century (Note – Julian dates are most likely to fall into 20th Century)

21st Century
=DATE(20&LEFT(A1,2),1,RIGHT(A1,3))

20th Century
=DATE(19&LEFT(A1,2),1,RIGHT(A1,3))

Note – 19 or 20 can be replaced with some IF condition to put in right 19 or 20 depending upon the year. For example, year 82 is more likely to be in 20th century where year 15 is more likely to be in 21st century.