Smartsheet: Budget month starts on a particular day
Despite (or because of) being active on the Smartsheet Community (when I have time), occasionally I get emails to one of the several emails I have added to my profile there. Here’s one I got recently.
Hi Craig,
Can you please help me figure out a formula for this situation:
I have a ‘Start Date’ column and a ‘Budget Month’ Column. Anything that starts after the 24th of the month falls into the next months budget – For example something starting 1/24 will be in February’s budget.
For the ‘Budget Month’ column I need a formula that will look at the ‘Start Date Column’ and if the date in the start date column is before the 24th of the month, it should return that month in the ‘Budget Month’ column and if the date in the ‘Start Date’ Column is after the 24th of the month, it should return the next month in the ‘Budget Month’ column. Example shown in screenshot below.
Do you already have the formula for the month in text? (The answer was no.)
- Create a new column named [Budget Month Val]. This will capture the numeric value of the month.
-
In that column (row 23), this formula=IFERROR(IF(DAY([Start Date]23) >= 24, IF(MONTH([Start Date]23) = 12, 1, MONTH([Start Date]23) + 1), MONTH([Start Date]23)), “Not a date”)
will return a number from 1-12 for the month using the MONTH function.
The formula is ‘wrapped’ in the IFERROR function, because if the Start Date is empty, Smartsheet complains. I don’t like it when it does that.
After that, return the day of the month using the DAY function. If it is greater than or equal to 24, then it will be the next month, but we can’t just add 1, because 12 (December) + 1 would return 13, which as much as I would advocate Cotswold’s 13 month calendar, that isn’t the way the calendar currently works. If 12 (December), then return a 1, otherwise add 1 to the number of the month. If the day of the month is less than 24, just return the number of the month.
Would that last paragraph make more sense if written in pseudo-code? Probably.
End result, we now have a value of 1-12 for the month of the budget
3. In the [Budget Month] column (row 23), put this formula
IF([Budget Month Val]23 = 2, “February”,
IF([Budget Month Val]23 = 3, “March”,
IF([Budget Month Val]23 = 4, “April”,
IF([Budget Month Val]23 = 5, “May”,
IF([Budget Month Val]23 = 6, “June”,
IF([Budget Month Val]23 = 7, “July”,
IF([Budget Month Val]23 = 8, “August”,
IF([Budget Month Val]23 = 9, “September”,
IF([Budget Month Val]23 = 10, “October”,
IF([Budget Month Val]23 = 11, “November”,
IF([Budget Month Val]23 = 12, “December”,
“Unknown”))))))))))))
Write a Reply or Comment