How can i hide negative numbers in the following counting formula
My formula counts number of days past Due Date if no Completion Date, but is returning negative values for any task that is not past the Due Date.
=IF(ISDATE([Completion Date]@row), "", TODAY() - [Due Date]@row)
Any help is greatly appreciated!
Best Answer
-
Hi Brad!,
Yep, that fixed it perfectly. Thank you so much for your patience and assistance. It is interesting to me that there does not appear to be a way to stop counting a number when using TODAY().
Please take care Brad,
Julie
Answers
-
hi @julieg
This formula is saying if completion date field is a date, then days over due to completion is "blank", if NOT, Todays date - Due Date, which is giving you the negatives.
I thin this formula will solve your use case.
=if(ISDATE([Completion Date]@row), "",if(([due date]@row-today())<=0,"", today()-[due date]@row
Hope this helps, have a good night.
best,
Brad
www.MVPOPS.com
-
Hello Brad!
thanks so much for responding. I did try your formula, this is what I tried:
=IF(ISDATE([Completion Date]@row), "", IF(([Due Date]@row - TODAY()) <= 0, "", TODAY() - [Due Date]@row))
If you reference the screen shot above, all of the negative values are still present for any task that is not Overdue (past the Due Date). Essentially everything in the screen shot above is exactly the same except now, the actual Overdue item is showing blank, instead of the days past due.
The formula above when applied to a task that is Overdue (past the Due Date) now shows blank instead of the number of days past due. So the logic seems to be reversed. The Overdue value should show, and the negative values should be blank. I hope that makes since.
Anything we can do to adjust the formula to show the days past due for a task Overdue (past the Due date), and then hide the negative values from the formula for rows that are not past due?
Thanks so much for your help,
Julie
-
Hi Brad,
Also, wondering if we can show blank in the Days Past Due column if completion date is not blank. While we can't stop the formula from counting, we can at least hide the value when Completion Date is not blank.
Thanks again for all of your time and help!
Julie
-
Hi Brad,
My messages above are probably a little confusing. Here is what I've determined. The following formula is working fine, except, it is displaying negative numbers when a task is not past due.
=IF(ISDATE([Completion Date]@row), "", TODAY() - [Due Date]@row)
If we can solve the negative numbers - I think we are good! Please let me know if you have any ideas.
Thanks!
-
Hi Julie,
The issue with the original formula is that it will continually run the formula if the completion date has not been input. Nesting the if statements should stop the inputting. I had my today and due date math switched on original, sorry about that. Let me know how this one goes.
=IF(ISDATE([Completion Date]@row), "", IF((today()-[due date]@row) <= 0, "", if( TODAY()-[Due Date]@row>=0, TODAY() - [Due Date]@row
best,
Brad
www.MVPOPS.com
-
Hi Brad!,
Yep, that fixed it perfectly. Thank you so much for your patience and assistance. It is interesting to me that there does not appear to be a way to stop counting a number when using TODAY().
Please take care Brad,
Julie
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 461 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!