View previous topic :: View next topic |
Author |
Message |
KennyT
Joined: 02 Aug 2005 Posts: 317
|
Posted: Thu Oct 05, 2017 11:22 am Post subject: Converting Excel days to date string |
|
|
i've googled for this but can't find a satisfactory answer.
So, does anyone have in their backpocket the source for a routine to convert Excel "days" (since 1/1/1900) to a date string (or DD,MM,YYYY as separate values)?
TIA
K |
|
Back to top |
|
|
mecej4
Joined: 31 Oct 2006 Posts: 1886
|
|
Back to top |
|
|
KennyT
Joined: 02 Aug 2005 Posts: 317
|
Posted: Thu Oct 05, 2017 12:14 pm Post subject: |
|
|
Magic!
Thank you.
it's out by one day (for the date range i'm interested in) but i think i can code around that!!!!!!
K |
|
Back to top |
|
|
mecej4
Joined: 31 Oct 2006 Posts: 1886
|
|
Back to top |
|
|
JohnCampbell
Joined: 16 Feb 2006 Posts: 2554 Location: Sydney
|
Posted: Thu Oct 05, 2017 2:17 pm Post subject: |
|
|
This is an interesting problem, as Excel now includes 29/02/1900, which is not a valid date. I think that the old lotus/excel date formula was from 1/1/1904. Excel considers 1900 to be a leap year!
This has always caused problems for calculating tides, as tides are described as a series of sine curves for frequency and phase angle, based on time since 1/1/1900, so can not be correctly calculated using Excel.
I have never seen any mention of correcting this problem. The date should be considered as days since 31/12/1899 |
|
Back to top |
|
|
mecej4
Joined: 31 Oct 2006 Posts: 1886
|
Posted: Thu Oct 05, 2017 2:27 pm Post subject: Re: |
|
|
JohnCampbell wrote: | ...I think that the old lotus/excel date formula was from 1/1/1904. Excel considers 1900 to be a leap year!
|
See https://en.wikipedia.org/wiki/Epoch_(reference_date) .
I think that 1/1/1904 was used as the reference date in MacOS and in Excel/Macintosh, but not in Excel/PC. According to the Wikipedia article, Excel/PC uses January 0, 1900 as the reference date. |
|
Back to top |
|
|
JohnCampbell
Joined: 16 Feb 2006 Posts: 2554 Location: Sydney
|
Posted: Sat Oct 07, 2017 3:25 am Post subject: |
|
|
Earlier versions of Excel on DOS/Windows as well as Lotus 123 and Quatro Pro did use 1904. There is a setting in Excel ( File > Options > Advanced > When calculating this workbook: "Use 1904 date system") to switch between the two date definitions. The amazing thing is that the 1900 date system is wrong !! although Microsoft's description does not confirm this. Just put in 28/Feb/1900 in Excel then add 1.
https://support.office.com/en-us/article/Date-systems-in-Excel-e7fe7167-48a9-4b96-bb53-5612a800b487
This has always annoyed me, as Tide formula are quoted based on midnight of 1/1/1900, which meant that Excel calculations were always out by 1 day, so there was always the confusion if it was Excel or the phase angle when you did not get the correct answer.
Apparently, from the Wiki link, Unix time will run out in 2037, but more significantly for us DOS (fat32/ntfs etc) date will overflow in 2044. What will we do with all our back-ups then ? I'll be in my 90's and hopefully I'll be around to notice it happen. The whole DOS/Windows file system will have to change.
John |
|
Back to top |
|
|
|