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!
Solved! Go to Solution.
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.
@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.
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
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.