forums.silverfrost.com Forum Index forums.silverfrost.com
Welcome to the Silverfrost forums
 
 FAQFAQ   SearchSearch   MemberlistMemberlist   UsergroupsUsergroups   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

Converting Excel days to date string

 
Post new topic   Reply to topic    forums.silverfrost.com Forum Index -> Support
View previous topic :: View next topic  
Author Message
KennyT



Joined: 02 Aug 2005
Posts: 247

PostPosted: Thu Oct 05, 2017 11:22 am    Post subject: Converting Excel days to date string Reply with quote

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
View user's profile Send private message Visit poster's website
mecej4



Joined: 31 Oct 2006
Posts: 732

PostPosted: Thu Oct 05, 2017 11:42 am    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
KennyT



Joined: 02 Aug 2005
Posts: 247

PostPosted: Thu Oct 05, 2017 12:14 pm    Post subject: Reply with quote

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
View user's profile Send private message Visit poster's website
mecej4



Joined: 31 Oct 2006
Posts: 732

PostPosted: Thu Oct 05, 2017 1:10 pm    Post subject: Reply with quote

Quote:
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".
Back to top
View user's profile Send private message
JohnCampbell



Joined: 16 Feb 2006
Posts: 1775
Location: Sydney

PostPosted: Thu Oct 05, 2017 2:17 pm    Post subject: Reply with quote

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
View user's profile Send private message
mecej4



Joined: 31 Oct 2006
Posts: 732

PostPosted: Thu Oct 05, 2017 2:27 pm    Post subject: Re: Reply with quote

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
View user's profile Send private message
JohnCampbell



Joined: 16 Feb 2006
Posts: 1775
Location: Sydney

PostPosted: Sat Oct 07, 2017 3:25 am    Post subject: Reply with quote

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
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    forums.silverfrost.com Forum Index -> Support All times are GMT + 1 Hour
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group