Smartsheet: Budget month starts on a particular day

2016-11-21How-To: Smartsheet HelpSmartsheet

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.

I don’t always answer them. Well, I typically answer the emails but don’t always provide the solutions. I sometimes get paid to solve problems like these and giving the answers away, for free, I’m told, is bad business. I have pretty smart friends. But … my time is (mostly) my own, I like helping people, and being able to (help) makes me feel a bit better after a long day. This time, I tossed the business person hat to the side and just decided to answer the friggin’ question.

The first thing I did was to ask a few questions of my own.

Do you already have the formula for the month in text? (The answer was no.)

If 24th (neither before or after, but on), what then?

That is 8/23 – August, 8/25 – September, what about 8/24? (The answer was September)

Even when solving what may be a simple problem, the missing pieces spring out at me and I try to plug the holes before they cause damage later.

If this had been a customer instead of just a casual question, there’s a few more questions that might spring to mind – does year matter? And of course why’s and who’s and so forth, but this is just a simple formula exercise and I’m not on the clock.

When building solutions in Smartsheet, I’m not typically concerned about adding columns. Some people are.

They (the columns, not the people) are easy to hide (not so easy to show again, but that’s a different issue). The intermediate formula results are often useful for other issues and I’d rather do the calculation once than twice or more on the same row. It is also easier to use for someone else to learn from.

The solution is pretty easy (I don’t mean to sound arrogant – I’ve been writing formulas in spreadsheets since Excel 2.0 or maybe 4.0 – or somewhere along then. Yes – that was a long time ago.) . Here is it:

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 = 1, “January”, 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”))))))))))))

That looks pretty intimidating – but it really isn’t. It is just a series of 12 IF statements nested to return the text of the month’s name.

Does this look easier to follow:?

=IF([Budget Month Val]23 = 1, “January”,
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”))))))))))))

Pro tip – I don’t write long complicated formulas like that in Smartsheet. I use UltraEdit but any nearly text editor will do. It makes it easier to follow, easier to update, easier to spot mistakes.

When copying the formula into Smartsheet, make sure you are in the edit mode or the paste will copy in 13 rows – not one formula.

And here’s the results with a few extra test cases for that wouldn’t be tested in the original data set.

There you go.

Smartsheet’s formulas can do some useful things to help you and your team work better together.

And if you get stuck – check out the Smartsheet Community. There are lots of people there that like to help other users.

p.s. The person asking for this caught me on a good night (for them) as I was trying to figure out what I was going to do while waiting for the next episode of Westworld.

Smartsheet: Budget month starts on a particular day

Write a Reply or Comment Cancel reply

Recent Posts

Recent Comments

Archives

Categories

Meta