community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
#SANTALYTICS

The highly anticipated Alteryx Community tradition is back! We hope you'll join us!

Learn More
We will be upgrading the Gallery to our 2019.4 release this Saturday, December 7th beginning at 9:00am MT. We expect the outage to take last approx. 2.5 hours.
SOLVED

How to Create Date from Excel Date Formatted as Number

Highlighted
Alteryx
Alteryx

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

 

 

Labels