N
New User
How do I convert julian date to calendar date?
IE: To:
4010 Jan 10 2004
4365 Dec 31 2004
IE: To:
4010 Jan 10 2004
4365 Dec 31 2004
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
P
Peo Sjoblom
R
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.
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.
N
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.
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.
N
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!
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!
N
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!
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!
R
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))
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))
N
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.
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.
N
New User
Check-out this site - I matched the results of your
formula to it.
http://personal.ecu.edu/mccartyr/isowdcal.html
formula to it.
http://personal.ecu.edu/mccartyr/isowdcal.html
R
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