Welcome to our forums
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
One possible solution (I found many by Google search):
Ascertain the Julian day of the epoch (01/01/1900). You can use the program http://www.ccpo.odu.edu/~klinck/SOGLOBEC/cruise/timedate/fortran/greg2jul.f for this purpose. Add the result to the 'Excel days', i.e., days since the epoch.
Then, pass the resulting value to the routine http://www.ccpo.odu.edu/~klinck/SOGLOBEC/cruise/timedate/fortran/gregorian.f and you will receive the results that you asked for.
I have not verified whether the codes that I referred to are correct.
Similar routines, but written using derived types:
http://flibs.sourceforge.net/libdate.html
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
it's out by one day
Perhaps this caution applies:
https://support.microsoft.com/en-us/help/214326/excel-incorrectly-assumes-that-the-year-1900-is-a-leap-year
Always take Excel results with a pinch of salt and a pillbox of aspirin!
Other useful resources:
http://www.public.asu.edu/~checkma/today.html
https://isotropic.org/date/
https://www.fourmilab.ch/documents/calendar/
The last link contains some comments regarding the Excel bug under 'Excel Serial Day Number'.
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
Quoted from JohnCampbell ...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.
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