Is there a way to have a column formula automatically change for each month of the year?

Box2105

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?

hollyconradsmith

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.

hollyconradsmith

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.