Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

Custom Date Time

chughes49
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!

5 REPLIES 5
neilgallen
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.

 

 

 

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

 

 

estherb47
15 - Aurora
15 - Aurora

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
17 - Castor
17 - Castor

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