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
-
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
-
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.
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 352 Global Discussions
- 198 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 443 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 283 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!