Convert julian date

J

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.
 
B

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
 
B

barry houdini

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

--

Regards,

Peo Sjoblom










- Show quoted text -
Click to expand...

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
 
R

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)))
 
M

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
 
M

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How do I get the date to display a Julian date? (yyddd) 2
Three Digit Julian Date 4
How to Convert a Julian Date to Calendar Date 2
Change satellite julian date format YYYYDDDHHMMSS to excel date ti 1
Calculating a Julian Date 7
Number of the day of the year 3
convert Julian date to DD/MM/YYYY 7
how to convert from julian date to mm/dd/year 6

Top