Date/Time Parsing Excel AM/PM format

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.

Parse

Date Time

Accepted answers

jdunkerley79

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.

DateTimeParsing.yxmd

All comments

jdunkerley79

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.

DateTimeParsing.yxmd

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.

DateTimeParseAMPM.yxmd

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

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!

NJT

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

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15865

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703