Calculating Days Remaining and Past Due
Hello everyone,
I am trying to calculate the days remaining and days past due for each row, however, I want it to stop count if the status is closed.
As you can see we have a "Days Remaining/Days Past Due" column and those closed (the grayed out rows) still calculate a number which is what I do not want.
I tried doing it myself but although it counts the days past due, it's not counting the days remaining.
We have various statuses other than open and in-progress. We have a total of 9 different statuses. Just for context.
Please help. Thank you.
Best Answer
-
Thanks for the info. Does this work for you then?
=IF(Status@row = "Closed", "", [Due Date]@row - TODAY())
Answers
-
What designates that a status is closed? At first I thought it was simply just having a date in the "Date of Closure" column but the first row has one and it's row isn't gray. If the first row is closed and not being gray is just an anomaly, then does this work?
=IF(ISBLANK([Date of Closure]@row), NETDAYS(TODAY(), [Due Date]@row), "")
-
Hi Mike :)
Thank you for your response. We manually change the status to "Closed". We conditionally formatted for a row to change to gray and green font when it's closed. The statuses are a drop-down list. As you can see one row we have as "Awaiting-Marketing" and this is one of 10 statuses you can choose from.
I also want to note that you can see that although we have a row as "Closed" it is still calculating days and I do not want that because counting days is no longer necessary for a closed request. I would like the days to stop being counted. I want it to keep count as long it is not "Closed" in the status column.
Currently the formula that sits in the days remaining/past due column is =[Due Date]@row - TODAY()
Hopefully that gives you more context.
-
Thanks for the info. Does this work for you then?
=IF(Status@row = "Closed", "", [Due Date]@row - TODAY())
-
😮 This is perfect and did the job! Thank you so much for your help :)
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!