Converting Date Time String to Date

[sean_carr]

I am bringing in date time data from excel in the form 11/25/2019 9:45:00 AM and alteryx reads it in as a Vstring 43794.40625. I tried using the datetime function to convert this to mm/dd/yyyy but it gives the error month is out of range. If someone can please give some direction on this.

Thanks,

Sean

[OllieClarke]

First convert your date field to a double, then use this formula:

DateTimeAdd(DateTimeAdd("1899-12-30 00:00:00",floor([date]),"days"),24*([date]-floor([date]))*60*60,"seconds")

 Ensure this field is a datetime

(Taken from this solution )

Ollie

edit: missed a closing bracket

[afv2688]

Hello @sean_carr,

Try and click on the input on the checkbox "first row contains data", this may solve your issue.

If not, is the file you are importing an xls or xlsx?

Regards

[CharlieS · Accepted solution]

So there's a few steps here, but it can all be done in one formula.

First, we need to convert the Excel format "43794.40625" to the Alteryx standard format (ISO8601), "YYYY-MM-DD". The Excel format is the days since 1900-01-01, so we need to add 43794 days from 1900-01-01 with this formula (the -2 compensates for the 0vs1 based index and final day position):

DateTimeAdd("1900-01-01",Round([Input],1)-2,"days")

This gives us 2019-11-25 00:00:000. So the next step is to add the .40625 portion of the day to that. So 0.40625 * 1440 (the minutes in a day) so we can add that many minutes to the previous formula result to bring it up to 9:45 AM. Wrapping this around the formula above you get: 

DateTimeAdd(DateTimeAdd("1900-01-01",Round([Input],1)-2,"days"),([Input]-Round([Input],1))*1440,"minute")

Now this gives us 2019-11-25 09:45:00. Now that we have the datetime value in the appropriate format, we can now convert this to the desired format using the DateTimeFormat( function:

DateTimeFormat(
DateTimeAdd(DateTimeAdd("1900-01-01",Round([Input],1)-2,"days"),([Input]-Round([Input],1))*1440,"minute")
,"%m/%d/%Y")

Which gives us 11/25/2019. If time is not desired, then you can skip the second step and just use this:

DateTimeFormat(
DateTimeAdd("1900-01-01",Round([Input],1)-2,"days")
,"%m/%d/%Y")