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
-
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
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!