Convert to a date from a text-field Month number
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.
Help Article Resources
Check out the Formula Handbook template!