Julian to Gregorian Date conversion - Can any one help!!!!
Julian to Gregorian Date conversion - Can any one help!!!!
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
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
- 10000 or more caches found
- Posts: 900
- Joined: 10 April 05 10:39 pm
- Location: Leeming Western Australia
Re: Julian to Gregorian Date conversion - Can any one help!
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.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
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.
-
- 1600 or more caches found
- Posts: 212
- Joined: 15 March 05 10:30 pm
- Location: Wayville, SA
- Contact:
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!
=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!
Last edited by shonkylogic on 27 July 06 10:07 pm, edited 1 time in total.
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
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
-
- 1600 or more caches found
- Posts: 212
- Joined: 15 March 05 10:30 pm
- Location: Wayville, SA
- Contact:
- caughtatwork
- Posts: 17019
- Joined: 17 May 04 12:11 pm
- Location: Melbourne
- Contact:
-
- Posts: 36
- Joined: 03 September 05 12:50 pm
- Location: Melb