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

  • ShelbyWarren
    ShelbyWarren ✭✭✭✭✭
    edited 04/05/23 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

  • ShelbyWarren
    ShelbyWarren ✭✭✭✭✭
    edited 04/05/23 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())

  • ShelbyWarren
    ShelbyWarren ✭✭✭✭✭
    edited 04/05/23

    @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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!