Julian to Gregorian Date conversion - Can any one help!!!!

nibbler · Post by **nibbler** » 27 July 06 8:52 pm

Hi,

I can find date conversion formulas in Excel for 5 digit Julian dates but I cant find one for a 6 digit Julian date, ie 105001 (1/01/2005).

This is actually for work in the morning, nothing to do with caching but........ Read previously that cachers are a wealth of knowledge on just about everything.

Regards, Team Nibbler

Chwiliwr · Post by **Chwiliwr** » 27 July 06 9:36 pm

nibbler wrote:Hi,

I can find date conversion formulas in Excel for 5 digit Julian dates but I cant find one for a 6 digit Julian date, ie 105001 (1/01/2005).

This is actually for work in the morning, nothing to do with caching but........ Read previously that cachers are a wealth of knowledge on just about everything.

Regards, Team Nibbler

If you define a Julian date as the number of days since the beginning of the year (1st January) there can only be 5 or 7 digits depending on whether you are using 2 or 4 digit years.

I couldn't make sense of the example you gave but once Excel knows that a cell is a date it can display it in a lot of ways by formating the cell. The trick is to make sure Excel knows that you are entering a date in the first place.

I know that hasn't answered your question but it is the best I could offer without some more info, especially if you mean something else by the term Julian date.

shonkylogic · Post by **shonkylogic** » 27 July 06 10:07 pm

Try this formula... Substitute your cell containing the julian date where you see 'A1'<p>

=DATEVALUE(CONCATENATE("01/01/",INT(1900+A1/1000)))+MOD(A1,1000)-1 <p>

You may need to set the display cell format as 'Date'.<p>

p.s. if it works you owe me a beer!

nibbler · Post by **nibbler** » 27 July 06 10:07 pm

Hi
Thanks for the reply. The example I have is 105001 where the first 1 indicates the 21st century, the 05 is the year and the 001 is the number of days in the year. This data has come from a SQL database and i am trying to generate a Gegorian date in Excel.

I can find formulas to convert 05001 but not the format from the SQL database.

Any help appreciated

nibbler · Post by **nibbler** » 27 July 06 10:09 pm

Thanks I will put the beer on ice and try the formula.

shonkylogic · Post by **shonkylogic** » 27 July 06 10:12 pm

If you're using a mac, you may need to change 1900 to 1904

Chwiliwr · Post by **Chwiliwr** » 27 July 06 10:21 pm

The way you are getting it from the SQL database is an odd format and not how it stores it internally so the extraction must be converting/formating it. Do you have an option to ask for it the right way.

Post by **caughtatwork** » 27 July 06 10:23 pm

PeopleSoft?
That's how they store it

nibbler · Post by **nibbler** » 27 July 06 10:27 pm

Yeah Peoplesoft . Thanks to all for the replies will try the shonkylogic formula in the morning.

Chwiliwr · Post by **Chwiliwr** » 27 July 06 10:49 pm

Try this one.

=DATE(IF(0+(LEFT(A1,1))=1,2000,1900)+MID(A1,2,2),1,RIGHT(A1,3))

You may need to make sure that cell is actually 6 characters long before applying formular.

This is just the usual conversion plus some mods to account for the initial 1.

arthurking83 · Post by **arthurking83** » 29 July 06 6:38 pm

Some more excel formulas....

http://excel-formulas.blogspot.com/2005 ... rsion.html

Geocaching Australia

Julian to Gregorian Date conversion - Can any one help!!!!

Julian to Gregorian Date conversion - Can any one help!!!!

Re: Julian to Gregorian Date conversion - Can any one help!