Flipping data within a column

[swilson9]

Hi All, 

I am trying to flip a date column from being mm.yyyy to yyyy/mm so that I can then order it by descending order whereby the the months are in chronological order by year also. 

I wonder if anyone can help?

[DavidThorpe · Accepted solution]

HI @swilson9 

The formula right([OldField],4)+'/'+left([OldField],2) should do this for you.

Please see the attached 

Thanks 

[LordNeilLord · Accepted solution]

Hey @swilson9 

The quick way would be to do: Right([Date],4)+"/"+Left([Date],2)

The correct way would be: DateTimeFormat(DateTimeParse(Date,"%m.%Y"), "%Y/%m")

[AlteryxUserFL · Accepted solution]

Here are two examples that accomplish your goal 🙂 

Let me know if you have any questions.  

[swilson9]

Thank you !! Works perfectly 

[swilson9]

Ah brilliant - great to learn both ways 🙂 

[danilang]

Hi @swilson9 

there's always the Regex route as well

REGEX_Replace([Date In], "(\d\d)\.(\d\d\d\d)", "$2/$1")

This looks for a group of 2 digits followed by a period and then a group of 4 digits.  The replacement outputs the second group, a "/"
 and then the 1st group

Dan