We are updating the requirements for Community registration. As of 7/21/21 all users will be required to register a phone number with their My Alteryx accounts. If you have already registered, you will be prompted on your next login to add your phone number.

Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
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")
lalit_sankhla
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
16 - Nebula
16 - Nebula

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