JULIAN DATE??

Summary of Julian date conversions

Summary re conversion of Julian Dates :-

In all of the conversion examples below the date 6 July 2000 has been used and the data for conversion is in cell A1. Also, the formulas assume that the Excel 1900 date system is being used.

DEFINITIONS
Format of Julian dates : yyyyddd or yydd
Value of Julian dates : No. of days since 1 Jan, 4712 BC
Format of Excel dates : Various (e.g. mm/dd/yy)
Value of Excel dates : No. of days from start of Excel date system

IMPORTANT DATES
Start of 1900 Excel date system : 1 Jan, 1900
Equivalent Julian date value : 2415019
Start of 1904 Excel date system : 1 Jan, 1904
Equivalent Julian date value : 2416480

CONVERSIONS
The first four formulas are based on info at www.cpearson.com)
1.Julian formatted date (2000188) to Excel date (07/09/00)
=DATE(LEFT(A1,4),1,RIGHT(A1,3))
2.Julian formatted date (00188) to Excel date (07/09/00)
=DATE(IF(0+(LEFT(C1,2))<30,2000,1900)+LEFT(C1,2),1,RIGHT(C1,3))
3.Excel date (09/07/00) to Julian formatted date (2000188)
=YEAR(A1)&TEXT(A1-DATE(YEAR(A1),1,0),"000")
4.Excel date (09/07/00) to Julian formatted date (00188)
=RIGHT(YEAR(A1),2)&TEXT(A1-DATE(YEAR(A1),1,0),"000")
5.Julian date value (2451732) to Excel date value (36713)
=A1-2415019
6. Excel date value (36713) to Julian date value (2451732)
=A1+2415019

Hope this helps

Celia

Re: Summary of Julian date conversions

Correction:-
All the dates in the CONVERSION section should read 07/06/00.
Celia

Here's a neat Julian conversion trick I thought you all might appreciate. The magic number is: 1963647

My very brief research of Julian dates revealed that the whole point of the dating system was to be able to subtract two Julian dates to determine the number of days between.

This draws a perfect parallel to the Excel date serial number which was created with the same purpose in mind. (Should we spend time wondering why Julian date values were not used as the Excel serial number for a date?)

Therefore...
If the Julian date for 7/9/00 is:
2000188
and the Excel date value for 7/9/00 is:
36721

Then all you need to do to convert a Julian date to an Excel date value is subtract the difference which happens to be: 1963467

So, to convert any Julian date to a regular date just subtract 1963467 from the Julian date and format the resulting Excel date value as a date.

Likewise, to convert a regular date to Julian just add 1963467!

-Aaron :)

OOPS!

Looks like I might stand corrected once again.

After some further testing it looks like the previously mentioned technique falls out of sequence...

Oh well, back to the drawing board. :)

Here you go,
This code will only work for the year 2000. The basis of this code is the fact that the Julian dates last 3 digits are the days of this year. So the 36526 that I am adding is the amount of days to the Jan 1, 2000. So if you need a different year, just change the 36526 to a different number that represents say Jan 1, 2001. Hope this helps. Let me know

Ryan

Sub ChangeJulianDate
ActiveCell.Value = Right(ActiveCell.Value,3)+36526,"mm/dd/yy")
End Sub

NeverMind, i'm still working on it

I guess i never looked at what a julian date was. You're example of 2000188 is actually Mar 24, 764 AD. So either you just gave an example that was not real or your system isn't a julian date. Let me know, but i'll get back to the drawing board.

Ryan

Assuming the date is in A1 you can use this formula. If you want code, let me know!
=TEXT(RIGHT(A1,3)+36526,"mm/dd/yy")

Ryan

I've just had a similar problem at work needing to check hundreds of thousands of 8 digit dates from the mainframe to check for invalid dates.

This can be done in a macro, but if you can also do it with a simple formula as follows:

NB: as I am in the UK to me a date of 20000710 would be 10th July 2000. If to you this date would represent 7th October 2000 you'll need to transpose the RIGHTand MIDparts of this formula.

Assuming date in cell A1, in cell B1 type =concatenate(right(a1,2),"/",mid(a2,5,2),"/",left(a2,4)) This will produce a TEXT value of 10/07/2000. If you then multiply that text value by one, it will "transform" into a real date that you can then format whatever way you want.

That's the "quick and dirty" way of doing it. If you want the macro code that I used, drop me an email and I'll send it to you.

JAF

Aaron
I saw this one before, and yes it worked for some
but not all.....I was going to look @ it further,
but time always gets the better of you.
If you find anything further to add......would like
to know

Ivan OOPS! Looks like I might stand corrected once again. After some further testing it looks like the previously mentioned technique falls out of sequence... Oh well, back to the drawing board. :)

It depends on how Julian Date is defined

Mike
One way of doing it,based upon the "scientific" definition of Julian date, is provided at :-
http://aa.usno.navy.mil/AA/data/docs/JulianDate.html
Ada

Looks like I might stand corrected once again. After some further testing it looks like the previously mentioned technique falls out of sequence... Oh well, back to the drawing board. :)

AB
I think your method didn't work because you were using a different counting system for the Julian date versus the count system for the Excel date.

The Julian date for 9 July, 2000 is 2451735 (not 1963647).

The Julian Date for 31 Dec, 1899 is 2415019.

To arrive at the"Excel" date for 9 July 2000, subtract the second number from the first number and you get the value of the "Excel" date.

Ada