Need Smartsheet to Recognize Month/Year Format for Date Calculations"

System

This discussion was created from comments split from: Can you Customize a Date Format to be Month and Year only?.

kss5229

https://community.smartsheet.com/discussion/comment/428776#Comment_428776

I'm also interested in finding this answer. I want smartsheet to recognize month/year as a date so I can do calculations for events that fall within that month/year.

Lucas Rayala

@kss5229 — you can convert this to a date in a helper column and then perform any calculation on that helper column. Presumably you are hardcoding the month/year in a Number/Text column? Something like: "01/2024"? Are these consistently entered? If the dates are consistently entered like this "01/2024", where the month is always two digits and the year is always 4 digits, you could create a helper column of the Date type, with the column formula:

=DATE(YEAR(VALUE(RIGHT(MyDate@row, 4)), MONTH(VALUE(LEFT(MyDate@row,2)), 1)

Replace "MyDate" with the title of your actual column. This formula would provide a true date, which you can do calculations on. Note that this defaults the date to the first of the month. There are other approaches depending on your use-case and the calculations you need to perform.

kss5229

My sheet already has 2 date helper columns to bookend each month (01/01/2024 as the start date and 01/31/24 as the end date). The hope would be to eliminate the actual date helper columns and instead have a column that just says Jan2024 (in whatever format to just identify month & year). From there I would want to use the month/year column in calculations rather than having calculations that look for dates >= start date and < = end date.