Is there a way to have a column formula automatically change for each month of the year?
I am tracking financials and we have two important numbers coming out of a row across 12 columns labeled by month. The first number is the "forecast at completion". Its a simple sum of all 12 columns, regardless of it being past of future month. The second number is the "forecast to completion" which is the sum of only the future month columns. Currently I edit the formula on the first day of every month. Is there a way to do this automatically based on the date?
Best Answer

Hi @Box2105
I am imagining that you could use a nested if formula for this.
=IF(LEFT(TODAY(), 2), 01, sum(jan:dec), IF(LEFT(TODAY(), 2), 02, sum(feb:dec),....and so on.
There may be a more elegant solution, but this is what pops into my head.
Holly Conrad Smith
Director of Technology & Innovation 💡 at Streamline
CliftonStrengths Top 5: Deliberative, Restorative, Achiever, Consistency, Harmony
Answers

Hi @Box2105
I am imagining that you could use a nested if formula for this.
=IF(LEFT(TODAY(), 2), 01, sum(jan:dec), IF(LEFT(TODAY(), 2), 02, sum(feb:dec),....and so on.
There may be a more elegant solution, but this is what pops into my head.
Holly Conrad Smith
Director of Technology & Innovation 💡 at Streamline
CliftonStrengths Top 5: Deliberative, Restorative, Achiever, Consistency, Harmony
Help Article Resources
Categories
Check out the Formula Handbook template!