Date conversion

[New User]

How do I convert julian date to calendar date?

IE: To:
4010 Jan 10 2004
4365 Dec 31 2004

 

[Peo Sjoblom]

See:

http://www.cpearson.com/excel/jdates.htm

 

[Roger Govier]

Hi

With the Julian Date in cell A1
=DATE(YEAR(TODAY()),MONTH(1),DAY(0)+RIGHT(A1,3))

Incidentally, 4365 is 30th December 2004. Leap Year this year.

 

[Norman Harker]

Hi New User!

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

Both of your examples interpret correctly (after noting that 2004 is a
Leap Year)

It assumes the left character is a year between 2000 and 2009

Where the day arguments exceeds the number of days in the month of the
month argument the month aggregates automatically.
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[New User]

AM I CONFUSED!

THE JULIAN DATE THAT I AM WORKING WITH IS IN CELL E4.
THE FORMULA THAT I AM WORKING WITH IS
=DATE(IF(0+(LEFT(A1,2))<30,2000,1900)+LEFT(A1,2),1,RIGHT
(A1,3))
I AM NOT SURE HOW TO APPLY E4 TO THE ABOVE FORMULA.THE
JULIAN DATE RANGE COVERS 2003 THROUGH 2004.

THANKS AGAIN FOR THE HELP!

 

[New User]

AM I CONFUSED!

THE JULIAN DATE THAT I AM WORKING WITH IS IN CELL E4.
THE FORMULA THAT I AM WORKING WITH IS
=DATE(IF(0+(LEFT(A1,2))<30,2000,1900)+LEFT(A1,2),1,RIGHT
(A1,3))
I AM NOT SURE HOW TO APPLY E4 TO THE ABOVE FORMULA.THE
JULIAN DATE RANGE COVERS 2003 THROUGH 2004.

THANKS AGAIN FOR THE HELP!

 

[Roger Govier]

Hi

I think the problem arises with definitions of Julian Date.
Peo's reference to Chip's site deals with what most people would regard as
Julian Date.

I guess you use the same thing as here at our factory, where everybody
thinks of the Julian date as being a single digit of the year number
followed by the day number within that year.

With your date in cell E4, modify my original formula to
=DATE(YEAR(TODAY()),MONTH(1),DAY(0)+RIGHT(E4,3))

or better still, Norman's solution (much neater I think)
=DATE(LEFT(E4)+2000,1,RIGHT(E4,3))

 

[Norman Harker]

Hi All!

For astronomers Julian day numbers are the number of days since
1-Jan-4713 BC with Day 0 starting UTC 12:00 Noon on 1-Jan-4713 and Day
0 starting 24 hours later. See:

http://www.tondering.dk/claus/cal/node3.html#SECTION003150000000000000000

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[New User]

Check-out this site - I matched the results of your
formula to it.

http://personal.ecu.edu/mccartyr/isowdcal.html

 

[Ron Rosenfeld]

How do I convert julian date to calendar date?

IE: To:
4010 Jan 10 2004
4365 Dec 31 2004

If the date is in A1, and if you use the convention that translates two (or
one) digit dates preferentially to 1930-2029, then:

=DATE(INT(A1/1000)+1900+100*(A1<29366),1,0)+MOD(A1,1000)


--ron