Solved

# Julain date conversion

Posted on 2009-04-15
1,735 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
Question by:RBVIT
7 Comments

LVL 4

Expert Comment

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

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

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

LVL 81

Assisted Solution

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

A quick bit of research reveals:

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

Chris
0

LVL 59

Accepted Solution

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

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

## Join & Write a Comment Already a member? Login.

### Suggested Solutions

Email statistics and Mailbox database quotas You might have an interest in attaining information such as mailbox details, mailbox statistics and mailbox database details from Exchange server. At that point, knowing how to retrieve this information …
Resolve Outlook connectivity issues after moving mailbox to new Exchange 2016 server
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

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

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

#### Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!