How to Create Date from Excel Date Formatted as Number

[FFFool]

I've run into this issue. I have many files where the date that I need access to is formatted as a number in Excel. I did some research on the number and found that this number is the number of days that had passed since 1/1/1900. 

With this information at the ready, I created a calculation that loked at the date to determine if it was a number. If it was a number, then it did a DateTimeAdd Function of 1/1/1900 + the number formatted date field from excel. Otherwise it just returned the date. 

I quickly realized that almost all of my date fields were exactly 3 days off. So, I subtracted 3 days from the number formatted date field. This causes the date to be correct for 98% of my files. However, every so oftern(Particularly a file that should have had the date 12/1 and another that should have had the date 12/7 require that the subtraction be only 2 days, rather than 3. 

If I can identify the cause, I could at least build an IF function for it, but even that makes me nervous unless I know the root issue. Obviously writing an excel macro to fix the date formats is possible, but I really would like it to be my last resort incase I run into this issue again. 

Any help is appreciated. 

Thank you,

[MikeB]

Hi @amit5182,

Please see my post above dated: 01-28-2016 09:48 AM

The double value stored in Excel will have a whole number portion that relates to the number of days.

The value may also have a fractional portion to the right of the decimal point. That value represents a fraction of a day.

For example, if you have a value of .5  that would be at noon (12pm) on that date.

Alternatively,  .25  would represent 6am and so on.

There are commonly used algorithms you can find online that are used to convert the fractional value to a time value.

Here is a quick example (this is just one of many potential ways you could calculate this):

note: this is C++ but you can use the mod() function in the formula tool to calculate the modulo - instead of modf() as used here. It's the logic here that is important.

double excelDateTime = doubleValueFromExcel

excelDateTime += 0.5 / (24 *60 * 60); // offset by 1/2 second to avoid rounding errors

double days, hours, mins, secs;       // these should probably be initialized

double hours_minutes = modf(excelDateTime, &days) *24.0;           // set days to days

double minutes_seconds = modf(hours_minutes, &hours) * 60.0;       // set hours to hours

double seconds_remaining = modf(minutes_seconds, &mins) * 60.0;    // set mins to minutes

modf(seconds_remaining, &secs);                                    // set secs to seconds

something like the above code will determine the days, hours, minutes and seconds from the double stored in Excel.

NOTE: the above code is not tested, it's meant to be an example of how you can convert an Excel datetime double value into the components needed for a DateTime object.

I hope this is helpful