This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
The highly anticipated Alteryx Community tradition is back! We hope you'll join us!
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