Convert to a date from a text-field Month number

Kelly Moore

Hello Smart(sheet) Friends

I have a smartsheet to record safety audit results based on audits in different locations from various teams. To re-use the same schedule each year without having to update, the schedule is listed Jan1H (1st half of a month, in this case January, ie, meaning the audit should be completed roughly anytime Jan 1st - Jan 15th), or Jan2H (2nd half of a month, meaning to complete anytime day 16- end of month). I would like to create a DueDate so I can automate reminders, flag late results, etc. I'm not worrying with WorkDays or exactly when the end of month is (although this would be a nice to have). I'm okay if I call the End of Month the 28th (yes I'll ignore leap years). I don't know how to create a date from the MonthNumber.

I have a column for Month Numbers (1, 2, etc). I have MonthHalf as 1 or 2. I also have a checkbox column (MonthIsNowchecking) if the MonthNumber is current Month, if that is needed.

Basically I would like

IF MonthHalf@row = 1 then create a date using MonthNumber and Day = 15 (If this was January 2020 then created date would be 01/15/2020 in US date format dd/mm/yyyy)

If MonthHalf@row = 2 then create a date using the MonthNumber and Day = 28 (the actual end of month date would be the perfect solution). Example if this was January 2020, then the created date would be 01/28/2020.

I do need the date to act as an actual date value and not text so I can use in automated workflows.

Thank you so much for all of your genius help.

Kelly

Kelly Moore

I did it with the column LateDate (see previous comment). I realized that if the current month was December then the formula MonthNumber + 1 wouldn't be valid if looking for Jan 1st, (next year). The last clause of the formula (false statement of MonthNumber<12) forces the month to January and pushes the current year to the next year.

LateDate equals

=IF(MonthNumber@row < 12, IF([Month Half]@row = 1, DATE(YEAR(TODAY()), MonthNumber@row, 16), DATE(YEAR(TODAY()), MonthNumber@row + 1, 1)), IF([Month Half]@row = 1, DATE(YEAR(TODAY()), MonthNumber@row, 16), DATE(YEAR(TODAY()) + 1, 1, 1)))

Kelly Moore

I thought more about finding the end of month and maybe it would be easier to to create a LateDate which would be the 16th of the current month and the 1st of the next month? That would eliminate the variability of the last day of the month. A LateDate would work since ultimately this is what any automation that I create is trying to make visible. - Kelly

Kelly Moore

I did it with the column LateDate (see previous comment). I realized that if the current month was December then the formula MonthNumber + 1 wouldn't be valid if looking for Jan 1st, (next year). The last clause of the formula (false statement of MonthNumber<12) forces the month to January and pushes the current year to the next year.

LateDate equals

=IF(MonthNumber@row < 12, IF([Month Half]@row = 1, DATE(YEAR(TODAY()), MonthNumber@row, 16), DATE(YEAR(TODAY()), MonthNumber@row + 1, 1)), IF([Month Half]@row = 1, DATE(YEAR(TODAY()), MonthNumber@row, 16), DATE(YEAR(TODAY()) + 1, 1, 1)))

Genevieve P.

Hi @KDM

Wow, well-done! It looks like you're the genius. 🤩