Conversion of date formats

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.

Best Answer

  • Peggy Parchert
    Peggy Parchert ✭✭✭✭✭✭
    Answer ✓

    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

Answers

  • Peggy Parchert
    Peggy Parchert ✭✭✭✭✭✭
    Answer ✓

    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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!