)
Lynn Hanna said:
How does one convert a julian date back to regular date?
hi there
see
http://www.cpearson.com/excel/jdates.htm (handy link to bookmark)
Converting A Standard Date To A Julian Date
The formula below will convert a standard Excel date in A1 to a Julian Date.
=RIGHT(YEAR(A1),2)&TEXT(A1-DATE(YEAR(A1),1,0),"000")
This formula takes the 2 right-most characters of the YEAR of the date in
A1, and then appends the number of days between the date in A1 and the 0th
day of that year. The TEXT function formats the day-of-year number as three
digits, with leading zeros if necessary.
Converting A Julian Date To A Standard Date
The formula below will convert a Julian date to a standard Excel date.
=DATE(IF(0+(LEFT(A1,2))<30,2000,1900)+LEFT(A1,2),1,RIGHT(A1,3))
If the year digits of the Julian date are less than 30 (i.e., 00 to 29), the
date is assumed to be a 2000 century year. If the year digits of the Julian
date are greater than or equal to 30 (i.e., 30 to 99), the date is assumed to
be a 1900 century year. This formula works by taking advantage of the fact
that the DATE function can handle days beyond the "normal" days in a month.
For example, DATE correctly computes 100-Jan-1999 to be 10-April-1999.
These Julian dates must have the leading zero or zeros for years between
2000 and 2009. For example the 123rd day of 2000 must be entered as 00123.
Format the cell as TEXT before entering the data, or enter an apostrophe
before the Julian date -- e.g., '00123. This will prevent Excel from
treating the Julian date as a number and suppressing the leading zeros.
