Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
It's the most wonderful time of the year - Santalytics 2020 is here! This year, Santa's workshop needs the help of the Alteryx Community to help get back on track, so head over to the Group Hub for all the info to get started!
SOLVED

Custom Date Time

Highlighted
8 - Asteroid

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!

Highlighted
12 - Quasar

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.

 

 

 

Highlighted
Alteryx
Alteryx

@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.

 

 

Highlighted
14 - Magnetar
14 - Magnetar

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

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

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")

 

 

Highlighted
Alteryx
Alteryx

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.Annotation 2019-07-19 095139.png

Labels