Rolling Monthly % Complete Formula

Sheri.C

Hello,

I cant figure out a formula for what I need and I need some help please!

I am looking for a formula so the % complete will only show a value in the previous month(s) and the current month. For right now I would like November and onwards to show up as 0, but when it becomes November then I would like November to show a value and December and onwards as a 0, etc.

Thank you,

Sheri

Christian G.

Compare to Today() and IF Month(Today()) is ">" than column month, write 0.

You need a row with the month number (jan =1, feb =2, etc) to compare. Lets make it row 1. The formula for december will be:

=if(month(today())>[December 2022]1,0,([December 2022]9/$[Dashboard Totals]5)+[November 2022]@row))

Drag the formula to the other columns

Sheri.C

The formula works for the Jan-May months, but not the Oct -Dec months.

To correct this I changed the>to< and it worked

However, my concern is when the Month(Today()) becomes Jan-May (1-5), the Oct-Dec values will change to 0 when they should not.

Christian G.

You'll have to add the year in the condition. Suppose you have the year on line 2.

=if(and(year(today())>=[December 2022]2,month(today())<[December 2022]1),0,([December 2022]9/$[Dashboard Totals]5)+[November 2022]@row))

I usually have rows like this at the top of my table:

In this exemple I'm looking at the past weeks, but it is the same for the futur weeks.

From top to bottom: Week away form this week, Year,Month,day and the last one is a concatenated display

Tha column [Cette Semaine] point at this week's saturday.

With these rows you can easily refer to the day, mouth or year for any formula.

I hope it help