Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
to convert a julian date back to regular date
How does one convert a julian date back to regular date?
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
to convert a julian date back to regular date
)
"Lynn Hanna" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to convert julian date to regular calendar date | Excel Worksheet Functions | |||
how do I make a word typed in a cell go to a specific cell in anot | Excel Discussion (Misc queries) | |||
How do I convert a number formated as a date to text in Excel? | Excel Discussion (Misc queries) | |||
julian date | Excel Discussion (Misc queries) | |||
Roll back to previous date | Excel Worksheet Functions |