topic Re: Formula to getting correct date in Alteryx Designer Desktop Discussions

Formula to getting correct date

chughes49 — Thu, 27 Jun 2019 19:12:56 GMT

Hey yall, I currently have this but I am trying to create 3 separate formulas that create "Jan" , "2019" , and "Jan 2019". Anything that someone can do would help so much. Thanks!

Re: Formula to getting correct date

DiganP — Thu, 27 Jun 2019 19:17:40 GMT

@chughes49 One way to do it would be using the Text to columns tool to parse out the date field. Attached is the workflow. Hope this helps.

Re: Formula to getting correct date

Kenda — Thu, 27 Jun 2019 19:17:55 GMT

Hi @chughes49

I would recommend using the following expressions in your Formula tool:

Month:

REGEX_Replace([Field1], ".*\-\s(.*)\s.*", "$1")

Year:

tonumber(REGEX_Replace([Field1], ".*\-\s.*(\d{4})", "$1"))

Both:

REGEX_Replace([Field1], ".*\-\s(.*)", "$1")

Re: Formula to getting correct date

chughes49 — Thu, 27 Jun 2019 19:24:47 GMT

Hi, @Kenda

thanks so much, that was very helpful. However, the month and both formulas are the same which give both. What would need to be changed for it to be just "Jan'?

Re: Formula to getting correct date

Kenda — Thu, 27 Jun 2019 19:28:02 GMT

@chughes49 Does that happen if there is no space between your month and year? If so, try this expression to get your month:

REGEX_Replace([Field1], ".*\-\s(.*)\d{4}", "$1")

Re: Formula to getting correct date

chughes49 — Thu, 27 Jun 2019 19:29:41 GMT

@Kenda , Yeah your right! Thanks!

Re: Formula to getting correct date

chughes49 — Thu, 27 Jun 2019 19:42:47 GMT

@Kenda How might that formula differ to get the same output but with "2019-01-31"?

Re: Formula to getting correct date

Kenda — Thu, 27 Jun 2019 19:50:06 GMT

@chughes49 So this is sort of a gross expression, but if you want the last day of the month given, use this:

DateTimeAdd(DateTimeAdd(DateTimeParse(REGEX_Replace([Field1], ".*\-\s(.*)", "$1"),"%b%Y"),1,"month"),-1,"days")

Basically, you get your month/year combo into a date format, but it will return the first day of the month. So then you must add one month then subtract one day to get the last day of your desired month.

Hope this helps!