Rolling Monthly % Complete Formula

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

Answers

  • Christian G.
    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
    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.
    Christian G. ✭✭✭✭✭✭
    edited 10/20/22

    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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!