Convert excel numeric date time value to alteryx date time

[amit5182]

Hello All,

I have a text file which contains a date field which holds data in the following format.

41974.48514

In excel it translates to      01-Dec-2014 11:38:36

When I read it into alteryx I am not able to recreate date time value from this number.

Kindly help.

Thanks

Amit

[JoshKushner]

This was solved previously here: https://community.alteryx.com/t5/Data-Preparation-Blending/How-to-Create-Date-from-Excel-Date-Format...

Hope this helps!

[amit5182]

Thanks Mike. 

I tried that already before posting. Time component is very important for me and the example in the other link gives only date.

Thanks

Amit

[JoshKushner · Accepted solution]

Try this:

DateTimeAdd(DateTimeAdd("1899-12-30 00:00:00",41974,"days"),24*.48514*60*60,"seconds")

[lalitsankhla]

I am facing the same issue.

For example Ihave a number 42808.62475

I applied the following formula:

Datetimeadd("1899-12-30 00:00:00", 42808.62475, "seconds").

I got the folowing ouptput:

1899-12-30 11:53:28

only the time changed but the date remained 1899-12-30

[DavidP]

You have to do both the days and seconds separately, like this:

DateTimeAdd(DateTimeAdd("1899-12-30 00:00:00",42808,"days"),24*.62475*60*60,"seconds")

[Jeremy1010110]

Here are the steps I took.  I used the previous posts formula.    

[Report_Date] is the field from Excel. 

[Date_Calc] is the integer from [Report_Date]

[Time_Calc] = [Report_Date] - [Date_Calc]

Adjusted Formula:

DateTimeAdd(DateTimeAdd("1899-12-30 00:00:00",[Date_Calc],"days"),86400*[Time_Calc],"seconds")