Custom Date Time

[chughes49]

Hey All,

I am bringing in an excel file and I am trying to do a Time date tool to get the output of "2019-01-01". However the data is "Fiscal 2019 - Jan 2019". Is there anyway to do a custom function or anything to obtain that as the output?

Thanks!

[neilgallen]

a datetime parse formula would work, but you'd need to get rid of the extra values in the string.

something like 

datetimeparse(REGEX_Replace([Field1],"(.*\s-\s?)(\l{3}\s\d{4})","$2"),"%b %Y")

would work.

[NickSm · Accepted solution]

@chughes49  - Can put that into a DateTime tool pretty easily using the custom option and 

"Fiscal 2019 - Month yyyy" as the incoming string field.  Could also trim the "Fiscal 2019" part or make it more flexible if needed.

Attaching the super quick example.

[estherb47]

Hi @chughes49 

Sure! You can use a formula tool.

datetimeparse(right([Field1],4)+"-01-01","%y-%m-%d")

Takes the last 4 digits in your date field, adds the month and day after, and then converts it into a Date. Just ensure that the new field is a Date data type.

You could also just use right([Field1],4)+"-01-01", and then a Select tool to convert into Date format.

Let me know if that helps

Cheers!

Esther

[CharlieS]

Here's a lengthy formula you can use in a Dynamic Rename tool to do this in one step.

datetimeparse(substring([_CurrentField_],findstring([_CurrentField_]," - ")+3),"%b %Y")

[Loic]

You would want to use "Text to Column" to extract the part of the string that contains the "date": "Jan 2019". Then use "DateTime" to convert the string "Jan 2019" to a date "2019-01-01".

Syntax for date format is %b yyyy - %b is abreviated month to 3 letters format. https://help.alteryx.com/2019.2/Reference/Functions.htm#Date/Time

attached workflow.