Julain date conversion

Dear Experts,
I have julian dates from Epicor financials I'd like to convert to normal date format dd/mm/yyyy in MS Excel.
I googled fixes and also checked here on EE but I can't seem to fix my problem.
For instance one of the dates is 733013 which should give me 03/12/2007.
However my results are not correct for the year portion when I use this formula:
=TEXT(A1+(21916),"dd/m/yyyy")
I keep getting 03/12/3966.
How can I fix this problem to get 03/12/2007 by using the same formula above (but corrected version) or any alternative formula?
Thank you kindly for your assistance.
RBVITAsked:
Who is Participating?
 
Chris BottomleySoftware Quality Lead EngineerCommented:
And of course adding the text formatting makes it:

=TEXT(DATE(1980, 1, 1 + A1-722815), "dd/m/yyyy")

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

Aren't Julian dates 5 digits long?
conversion functions at Pearson's site
http://www.cpearson.com/excel/jdates.htm 
Cheers
Dave
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
DaveCommented:
I had missed the earlier post as my post hung
But as per above, your 6 digit string does not appear to be a Julian date
Cheers
Dave
0
 
zorvek (Kevin Jones)ConsultantCommented:
The value 733013 is not a Julian date, either pure (Julian Day Number) or modern (as used in computer systems).

It also does not appear to be any form of Gregorian date given the date you believe it is. In other words, I can't find any relationship between the digits in the numeric value and the date it is supposed to represent.

If it is some obfuscated form of the Julian Day Number (the number of days since January 1, 4713 BC), then you might try this formula:

   =TEXT(A1-693860,"dd/m/yyyy")

At least it works with your specific date example.

Kevin
0
 
Chris BottomleySoftware Quality Lead EngineerCommented:
A quick bit of research reveals:

=DATE(1980, 1, 1 + A1-722815)

Chris
0
 
barry houdiniCommented:
If the number 733013 represents 3rd December 2007 then that's a count of days starting at 1st January 1 AD (assuming leap days applied throughout that period in the same way as the modern pattern).
I believe Kevin's suggestion will give 12/3 rather than 3/12 so to correct
=TEXT(A1-693594,"dd/m/yyyy")
Of course in Excel that will only work for dates on or after 1/1/1900
regards, barry
 
 
 
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.