Formula to getting correct date

[chughes49]

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!

I

[DiganP]

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

[Kenda · Accepted solution]

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

[chughes49]

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

[Kenda]

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

[chughes49]

@Kenda , Yeah your right! Thanks!

[chughes49]

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

[Kenda]

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