Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Julain date conversion

Posted on 2009-04-15
7
Medium Priority
?
2,020 Views
Last Modified: 2012-05-06
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.
0
Comment
Question by:RBVIT
7 Comments
 
LVL 4

Expert Comment

by:anvanster
ID: 24154918
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
 
LVL 50

Expert Comment

by:Dave Brett
ID: 24154929

Aren't Julian dates 5 digits long?
conversion functions at Pearson's site
http://www.cpearson.com/excel/jdates.htm 
Cheers
Dave
0
 
LVL 50

Expert Comment

by:Dave Brett
ID: 24154935
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
Making Bulk Changes to Active Directory

Watch this video to see how easy it is to make mass changes to Active Directory from an external text file without using complicated scripts.

 
LVL 81

Assisted Solution

by:zorvek (Kevin Jones)
zorvek (Kevin Jones) earned 400 total points
ID: 24155166
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
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 24155221
A quick bit of research reveals:

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

Chris
0
 
LVL 59

Accepted Solution

by:
Chris Bottomley earned 800 total points
ID: 24155231
And of course adding the text formatting makes it:

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

Chris
0
 
LVL 50

Assisted Solution

by:barry houdini
barry houdini earned 800 total points
ID: 24155754
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

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
Steps to fix error: “Couldn’t mount the database that you specified. Specified database: HU-DB; Error code: An Active Manager operation fail”
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
Suggested Courses

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question