Conversion of date formats

Rory McNamara · 03/16/23

Hi. I have an existing sheet that is populated with data and a specific date assigned to each row e.g. 16/03/2023. I would like to add another column that reads from the date cell and converts it to Month and Year. I am not precious about whether it converts to 03/2023 or Mar 2023. This date setting is not in the available formats so am expecting to need to generate a formula, which is where I need some help.

Peggy Parchert · 03/16/23

Hello @Rory McNamara

I created a separate metric sheet to complete the conversion of a date to that format.

I use this is several of my sheets/projects so that as the date changes (TODAY Helper column) my sheets/project update (roll). Saves me time from having to manually update things when the month rolls.

COLUMNS:

TODAY Helper: this is a date type column and is auto-populated by a workflow to record a date.

Count: This is a text type column and the numbers do not change

Month: This is a text type column

formula: =IF((MONTH([TODAY Helper]@row) + Count@row) < 13, MONTH([TODAY Helper]@row) + Count@row, MONTH([TODAY Helper]@row) + Count@row - 12)

Month Convert: This is a text type column

formula: =IF(Month@row = "12", "DEC", IF(Month@row = "11", "NOV", IF(Month@row = "10", "OCT", IF(OR(Month@row = "9", Month@row = "09"), "SEP", IF(OR(Month@row = "8", Month@row = "08"), "AUG", IF(OR(Month@row = "7", Month@row = "07"), "JUL", IF(OR(Month@row = "6", Month@row = "06"), "JUN", IF(OR(Month@row = "5", Month@row = "05"), "MAY", IF(OR(Month@row = "4", Month@row = "04"), "APR", IF(OR(Month@row = "3", Month@row = "03"), "MAR", IF(OR(Month@row = "2", Month@row = "02"), "FEB", IF(OR(Month@row = "1", Month@row = "01"), "JAN"))))))))))))

Month Convert2: This is a text type column

formula: =IF([Month Convert]@row = "DEC", "12", IF([Month Convert]@row = "NOV", "11", IF([Month Convert]@row = "OCT", "10", IF([Month Convert]@row = "SEP", "09", IF([Month Convert]@row = "AUG", "08", IF([Month Convert]@row = "JUL", "07", IF([Month Convert]@row = "JUN", "06", IF([Month Convert]@row = "MAY", "05", IF([Month Convert]@row = "APR", "04", IF([Month Convert]@row = "MAR", "03", IF([Month Convert]@row = "FEB", "02", IF([Month Convert]@row = "JAN", "01"))))))))))))

Year: This is a text type column

formula: =IF((OR((MONTH([TODAY Helper]@row) + Count@row) > 12, Count@row = 0)), YEAR([TODAY Helper]@row), YEAR([TODAY Helper]@row) - 1)

Converted Date: This is a text type column

formula: =JOIN([Month Convert2]@row:Year@row, "/")

If you wanted Mar 2023 instead of 03/2023, you would use the Month Convert column instead of Month Convert2 and move it next to Year column so the JOIN formula would work.

Note: the Converted Date is not a Date but text.

Hope this helps.

Peggy

Peggy Parchert · 03/16/23