Countdown for days until due but stop counting after due date has passed
Hiya, I am trying to write a formula that will count the number of days until a task is due but will not start showing negative numbers once the due date has gone by. I came up with
=NETWORKDAY([Today's Date]@row, [Date Next Check/Validation Due]@row)
But couldn't figure out how to make it be blank once the Date Next Check/Validation Due date passed.
I am also going to have a complementary column with a formula that counts how many days overdue it is, but would like it to be blank until it is actually overdue. I originally thought I could simply take the above formula and swap the column names, but it gave me negative values.
I've fiddled around with incorporating IF formulas but haven't had any luck.
Thanks for any suggestions y'all can come up with :)
Best Answer
-
@Elijah Try this for the days to the next check/validation:
=IF([Date Next Check/Validation Due]@row < TODAY(), "", NETWORKDAY(TODAY(), [Date Next Check/Validation Due]@row))
For the days overdue column, if you have a "Status" column that shows if the check/validation has been completed, use it in this formula, if you don't have one then you'll need to add one:
=IF([Date Next Check/Validation Due]@row >today(), "", IF(Status@row <> "Complete", NETDAYS([Date Next Check/Validation Due]@row, today())
Answers
-
@Elijah Try this for the days to the next check/validation:
=IF([Date Next Check/Validation Due]@row < TODAY(), "", NETWORKDAY(TODAY(), [Date Next Check/Validation Due]@row))
For the days overdue column, if you have a "Status" column that shows if the check/validation has been completed, use it in this formula, if you don't have one then you'll need to add one:
=IF([Date Next Check/Validation Due]@row >today(), "", IF(Status@row <> "Complete", NETDAYS([Date Next Check/Validation Due]@row, today())
-
@Elijah try this for the days to next check/validation:
=IF([Date Next Check/Validation Due]@row < TODAY(), "", NETWORKDAY(TODAY(), [Date Next Check/Validation Due]@row))
Try this for the days overdue - if you have a "Status" column already that shows if the check/validation has been completed, use it in this formula, if not you'll need to add one:
=IF([Date Next Check/Validation Due]@row > TODAY(), "", IF(Status@row <> "Complete", NETDAYS([Date Next Check/Validation Due]@row, TODAY())))
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.2K Get Help
- 419 Global Discussions
- 221 Industry Talk
- 461 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 142 Just for fun
- 58 Community Job Board
- 462 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!