Calculating Overdue days

Options

Hi,

Am using formula [ =TODAY() - [Due Date]@row ] to calculate overdue days, which is working fine but when the date is in the future it's showing me [ - ] minus sign and days (example [ -2] ).

Is there a way to have [ +2 ] if date is in the future.

Thanks in advance,

Best Answer

  • Protonsponge
    Protonsponge ✭✭✭✭✭
    edited 07/09/24 Answer ✓
    Options

    Hello @Saj

    Would the following be an option for you?

    =IF([Due Date]@row > TODAY(), "+" + ABS(TODAY() - [Due Date]@row), TODAY() - [Due Date]@row)

    I hope that is helpful to you in some way,

    Protonsponge

Answers

  • Protonsponge
    Protonsponge ✭✭✭✭✭
    edited 07/09/24 Answer ✓
    Options

    Hello @Saj

    Would the following be an option for you?

    =IF([Due Date]@row > TODAY(), "+" + ABS(TODAY() - [Due Date]@row), TODAY() - [Due Date]@row)

    I hope that is helpful to you in some way,

    Protonsponge

  • Saj
    Saj ✭✭✭
    Options

    Many thanks @Protonsponge that's exactly I was looking for.

  • Saj
    Saj ✭✭✭
    Options

    @Protonsponge, Sorry to come back to this again. Your formula is perfect but then I realize, if tasks is [completed or cancelled] it's still showing overdue days. So I tried to change your formula and are getting error.

    =IF(OR([Due Date]@row >= TODAY(), Status@row = "Completed", Status@row = "Cancelled"), "0", IF(AND([Due Date]@row >= TODAY(), OR(Status@row = "Not Started", Status@row = "In Progress"), "+" + ABS(TODAY() - [Due Date]@row), TODAY() - [Due Date]@row))

    What exactly am trying to do here is, if task Status is [ Complete / Cancelled ] show me [ 0 ] in Overdue days. However if task Status is [ Not Started / In Progress ] and Due date is in the past show me number of Overdue days and if Due date is in future show me number of days with plus [ + ] sign.

    Thank you.

  • Protonsponge
    Protonsponge ✭✭✭✭✭
    Options

    Hello @Saj,

    I am very sorry, I completely missed the status column in your initial screen shot and did not think to include in the first formula - my apologies.

    I think the formula below will give you what you are are looking for:-

    =IF(OR(Status@row = "Completed", Status@row = "Cancelled"), 0, IF(AND(OR(Status@row = "Not Started", Status@row = "In Progress"), [Due Date]@row > TODAY()), "+" + ABS(TODAY() - [Due Date]@row), TODAY() - [Due Date]@row))

    1. The first part asks IF the status is "Completed" OR "Cancelled" then "0".
    2. The second part asks IF the status is "Not Started" OR "In Progress" AND the [Due Date] is greater than today, then show TODAY minus [Due Date] as an ABSolute number - (Which in your case is the number of days to complete the task)
    3. The last part then, if none of the above is true then TODAY minus [Due Date] (Which in your case is the number of days the task is overdue)

    All seems to be working in the demo below.

    I hope that is helpful to you in some way,

    Protonsponge

  • Saj
    Saj ✭✭✭
    Options

    Awesome @Protonsponge, Yes that's very helpful and correct formula. 😄

  • Protonsponge
    Protonsponge ✭✭✭✭✭
    Options

    Great, I am pleased you got something that worked for you.

    I'm grateful for the opportunity to help you out,

    Protonsponge

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!