community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

Custom Date Time

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!

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.

 

 

 

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.

 

 

Pulsar

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

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

 

 

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