Date/Time Parsing Excel AM/PM format

[Domo]

Hello everyone,

I currently have excel as an input in my workflow containing dates/times that have a vlookup formula. When importing, Alteryx put's this in an excel format (i.e. 16/01/2017 9:45:09 AM) which isn't recognisable. Is there a formula I can use so it understands it a Date/time format (i.e. 2017-01-16 09:45:09)?

Thank you.

[jdunkerley79 · Accepted solution]

A little fiddly format to deal with but here goes:

IF REGEX_Match([DateString], "\d{1,2}/\d{1,2}/\d{4} \d{1,2}:\d{1,2}:\d{1,2} [AP]M") THEN
	DateTimeAdd(
	DateTimeParse(
		Replace(Trim(REGEX_Replace(" " + [DateString],"([/ :])(\d)(?=[/ :])","${1}0$2"))," 12:"," 00:"),
		"%d/%m/%Y %H:%M:%S %X"),
		IIF(Contains([DateString],"AM"),0,12), "hours")
ELSE
	[DateString]
ENDI

So to explain:

IF REGEX_Match([DateString], "\d{1,2}/\d{1,2}/\d{4} \d{1,2}:\d{1,2}:\d{1,2} [AP]M") THEN

tells the formula to only care deal with strings which match the format you supplied (with or without leading 0 in day, month, hour, minute or second parts).

The:

Trim(REGEX_Replace(" " + [DateString],"([/ :])(\d)(?=[/ :])","${1}0$2"))

adds all the missing leading zeros to the string.

The:

Replace(...," 12:"," 00:")

handles the fact that we need to treat 12 as 0 for parsing purposes.

Next the:

DateTimeParse(...,"%d/%m/%Y %H:%M:%S %X")

parses the string into Alteryx's date format. The %X is supposed to handle AM/PM but on mine seemed to be ignoring it so...

DateTimeAdd(...,IIF(Contains([DateString],"AM"),0,12), "hours")

corrects for PM.

Sample attached.

[MichaelCh]

The DateTimeParse function in the Formula tool supports AM/PM parsing with a 12-hour specifier.

DateTimeParse([ExcelDateField], '%d/%m/%Y %I:%M:%S %p')

Sample workflow attached.

[jdunkerley79]

Don't believe %I and %p or %P are supported in DateTimeParse prior to version 11.0: https://help.alteryx.com/10.6/index.htm#Reference/DateTimeFunctions.htm#Format

[MichaelCh]

Right you are! I did not realize that. Thanks for pointing that out. Something to look forward to, then.

[Domo]

Love you work jdunkerley. You solved my half day headache in one neat formula :)

[aclaxton]

That simple parse saved me so much time @MichaelCh!

[Ty]

Found this article yesterday Googling.

Below please find more details on a similar use case.

1st, thanks for the solutions above!

If you're having trouble with %p or %P, I was too.

Usecase: Passing AM/PM am/pm, when time appears to parse but AM/PM am/pm breaks...

AM/PM am/pm explained:

%p = upper case

%P = lower case

Hours explained:

%H	Hours in a twenty-four hour clock, from 00 to 23.
%I	The hour on a twelve hour clock, from 00 to 12.

%H will work if you have an AM + PM.. For me, %p %P breaks.

%I helped me pass %p.

From paste above:

%m/%d/%Y %I:%M:%S %p

and the specifiers work correctly in the DateTime tool.

Best,

Ty

[NJT]

This one works now so it's a lot easier than the Regex option, at least in 2019.1 it works.