Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Convert excel numeric date time value to alteryx date time

amit5182
6 - Meteoroid

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

6 REPLIES 6
JoshKushner
12 - Quasar
amit5182
6 - Meteoroid

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
12 - Quasar

Try this:

 

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

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
17 - Castor
17 - Castor

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
5 - Atom

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")

Labels