|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
|
Thread Tools | Display Modes |
|
#11
November 11th, 2009, 03:25 AM
posted to microsoft.public.excel.misc
|
|||
|
|||
Convert julian date
hi
see this site http://www.cpearson.com/excel/jdates.htm regard FSt1 "JHL" wrote: I have a julian format of YYDDD. The formula I'm using is off a day and a century. Formula =date(left(a1,2),1,(mid(a1,3,3)) 05059 = 3/31/1905 How can I get the correct answer of 2/28/2005? Thanks in advance. 
|
|
#12
November 11th, 2009, 03:25 AM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Convert julian date
Try =DATE(IF(0+(LEFT(A1,2))30,2000,1900)+LEFT(A1,2),1 ,RIGHT(A1,3))
-- Kevin "JHL" wrote: I have a julian format of YYDDD. The formula I'm using is off a day and a century. Formula =date(left(a1,2),1,(mid(a1,3,3)) 05059 = 3/31/1905 How can I get the correct answer of 2/28/2005? Thanks in advance.
|
|
#13
November 11th, 2009, 03:26 AM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Convert julian date
=DATE(IF(0+(LEFT(A1,2))30,2000,1900)+LEFT(A1,2),1 ,RIGHT(A1,3))
-- Kevin "JHL" wrote: I have a julian format of YYDDD. The formula I'm using is off a day and a century. Formula =date(left(a1,2),1,(mid(a1,3,3)) 05059 = 3/31/1905 How can I get the correct answer of 2/28/2005? Thanks in advance.
|
|
#14
November 11th, 2009, 03:31 AM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Convert julian date
=DATE(IF(0+(LEFT(A1,2))30,2000,1900)+LEFT(A1,2),1 ,RIGHT(A1,3))
Source: http://www.cpearson.com/excel/jdates.htm -- Kevin "JHL" wrote: I have a julian format of YYDDD. The formula I'm using is off a day and a century. Formula =date(left(a1,2),1,(mid(a1,3,3)) 05059 = 3/31/1905 How can I get the correct answer of 2/28/2005? Thanks in advance.
|
|
#15
November 11th, 2009, 04:49 AM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Convert julian date
What date do you think the 59th day of the year is, Mike?
-- David Biddulph "Mike H" wrote in message ... Hi, Why is 2/28/2005 the correct date for 05059? I can understand the first 05 being the year but how do we arrive at 28 Feb from the 059 bit? Mike "JHL" wrote: I have a julian format of YYDDD. The formula I'm using is off a day and a century. Formula =date(left(a1,2),1,(mid(a1,3,3)) 05059 = 3/31/1905 How can I get the correct answer of 2/28/2005? Thanks in advance.
|
|
#16
November 11th, 2009, 04:51 AM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Convert julian date
Thanks EVERYONE for the responses.
barry houdini, yours was the easiest to implement. "barry houdini" wrote: On Nov 10, 7:26 pm, "Peo Sjoblom" wrote: http://www.vertex42.com/ExcelTemplat...-calendar.html -- Regards, Peo Sjoblom "JHL" wrote in message ... I have a julian format of YYDDD. The formula I'm using is off a day and a century. Formula =date(left(a1,2),1,(mid(a1,3,3)) 05059 = 3/31/1905 How can I get the correct answer of 2/28/2005? Thanks in advance.- Hide quoted text - - Show quoted text - Hello JHL, Your formula has one parenthesis too many, I assume you are using this formula =DATE(LEFT(A1,2),1,MID(A1,3,3)) In which case I'm not sure how you get 3/31/1905, if I have 05059 in A1 I get 28th Feb 1905....so you only have to fix the year part as far as I can see. If all dates are this century try =DATE(LEFT(A1,2)+100,1,MID(A1,3,3)) regards, barry .
|
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
