Convert julian date

[JHL]

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.

 

[Peo Sjoblom]

http://www.vertex42.com/ExcelTemplates/julian-date-calendar.html

--


Regards,


Peo Sjoblom

 

[Bernard Liengme]

Your formula is giving me 28 Feb 1905 (this is correct the 59th day of a
year is 28 Feb; 3 Mar is day 62 in a non-leap year)

This =DATE(LEFT(A3,2)+100,1,(MID(A3,3,3))) gives me 28 Feb 2005
best wishes

 

[barry houdini]

http://www.vertex42.com/ExcelTemplates/julian-date-calendar.html

--

Regards,

Peo Sjoblom










- 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

 

[Rick Rothstein]

The problem with 2-digit years is knowing when the century is 1900 or 2000.
Pick a cut-off year (say 60) and assume anything less than that is in the
2000 century and anything equal to or greater is in the 1900 century...

=DATE(1900+100*(--LEFT(A1,2)<60)+LEFT(A1,2),1,(MID(A1,3,3)))

 

[Gary''s Student]

=DATE(2000+LEFT(A1,2),1,0)+RIGHT(A1,3)

 

[Mike H]

Ignore my previous post (wherever it went) I had an elderly moment, it's a
julian date i.e. day 59 of 2005, use this

=DATE(IF((LEFT(A1,2)+0)<30,2000,1900)+LEFT(A1,2),1,RIGHT(A1,3))

Mike

 

[Luke M]

=DATE(20&LEFT(B29,2),1,RIGHT(B29,3))

 

[Mike H]

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

 

[Teethless mama]

=DATE(LEFT(A1,2)+2000,1,RIGHT(A1,3))

 

[FSt1]

hi
see this site
http://www.cpearson.com/excel/jdates.htm

regard
FSt1

 

[AFSSkier]

Try =DATE(IF(0+(LEFT(A1,2))<30,2000,1900)+LEFT(A1,2),1,RIGHT(A1,3))

 

[AFSSkier]

=DATE(IF(0+(LEFT(A1,2))<30,2000,1900)+LEFT(A1,2),1,RIGHT(A1,3))

 

[AFSSkier]

=DATE(IF(0+(LEFT(A1,2))<30,2000,1900)+LEFT(A1,2),1,RIGHT(A1,3))
Source: http://www.cpearson.com/excel/jdates.htm

 

[David Biddulph]

What date do you think the 59th day of the year is, Mike?

 

[JHL]

Thanks EVERYONE for the responses.

barry houdini, yours was the easiest to implement.