Can I write a formula for past due calculation?

Hi,

A formula is updated to calculate the Past Due as "=IF(OR(PARENT(Description@row) = "Summary", PARENT(Description@row) = "Instructions"), "", IF([Due Date]@row = "", "", IF(AND(Status@row <> "Complete", Status@row <> "Canceled", [Due Date]@row < Today#), "Overdue", "Not due")))"

Hence in result it gives "Overdue" or "Not due". My question is, what formula should I apply to get result as "Due in # days" or "# days Overdue"

"For the Past Due column, does functionality exist to load a formula/function that results in more of a countdown? For example - compares today's date to due date and show #days until due, and then #days past due once we pass the due date?  

Many Thanks!

Archana Sahu

Best Answers

  • Heather Duff
    Heather Duff ✭✭✭✭✭✭
    Answer ✓

    Hi @Archana Sahu ,


    It sounds like your question is primarily about the TODAY function. Here's a great resource: https://help.smartsheet.com/function/today

    TODAY() can be used with a negative number for the number of days before today (example: TODAY(-2) means two days before today), or with a positive number for the number of days after today (example: TODAY(2) means two days after today).

    So, if you're wanting to calculate the number of days until something is due (or number of days overdue), you could use a formula like this:

    =TODAY()-[Due Date]@row

    This formula will display a positive number if the due date has not yet passed, 0 if the due date is today, and a negative number if the due date has already passed.


    Hope this helps!


    Best,

    Heather

  • Heather Duff
    Heather Duff ✭✭✭✭✭✭
    Answer ✓

    @Archana Sahu You could, actually! It would look something like this:

    =IF((TODAY()-[Due Date]@row)>0,"Due in " + (TODAY()-[Due Date]@row) + "days",IF((TODAY()-[Due Date]@row)=0,"Due today",IF((TODAY()-[Due Date]@row)<0,-(TODAY()-[Due Date]@row)+" days overdue","")))

    Try that and see if it works; it may need a little tweaking.

  • Heather Duff
    Heather Duff ✭✭✭✭✭✭
    Answer ✓

    @Archana Sahu Glad you got it to work! :)

Answers

  • Heather Duff
    Heather Duff ✭✭✭✭✭✭
    Answer ✓

    Hi @Archana Sahu ,


    It sounds like your question is primarily about the TODAY function. Here's a great resource: https://help.smartsheet.com/function/today

    TODAY() can be used with a negative number for the number of days before today (example: TODAY(-2) means two days before today), or with a positive number for the number of days after today (example: TODAY(2) means two days after today).

    So, if you're wanting to calculate the number of days until something is due (or number of days overdue), you could use a formula like this:

    =TODAY()-[Due Date]@row

    This formula will display a positive number if the due date has not yet passed, 0 if the due date is today, and a negative number if the due date has already passed.


    Hope this helps!


    Best,

    Heather

  • Many thanks!

    I already had above solution, I was just asking if we can write number of days and 'Not due'/'Overdue' together. Anyways I will add one more column for #days.

    Regards,

    Archana Sahu

  • Heather Duff
    Heather Duff ✭✭✭✭✭✭
    Answer ✓

    @Archana Sahu You could, actually! It would look something like this:

    =IF((TODAY()-[Due Date]@row)>0,"Due in " + (TODAY()-[Due Date]@row) + "days",IF((TODAY()-[Due Date]@row)=0,"Due today",IF((TODAY()-[Due Date]@row)<0,-(TODAY()-[Due Date]@row)+" days overdue","")))

    Try that and see if it works; it may need a little tweaking.

  • Thank you so much @Heather Duff !!!

    It worked for me.

    Regards,

    Archana Sahu

  • Hi @Heather Duff

    The formula worked for me, however the results are wrong, it means 'n days overdues' are reflecting as 'due in n days', and 'due in n days' are reflecting as 'n days overdues'.

    I tried to fix it, but couldn't fix it. Could you please help.

    Regards,

    Archana Sahu

  • Hi @Heather Duff

    I could fix it as "=IF((TODAY() - [Due Date]@row) < 0, "Due in " + ([Due Date]@row - TODAY()) + " days", IF((TODAY() - [Due Date]@row) = 0, "Due today", IF((TODAY() - [Due Date]@row) > 0, +(TODAY() - [Due Date]@row) + " days overdue", "")))"

    Thank you for your time and help!

    Regards,

    Archana Sahu

  • Heather Duff
    Heather Duff ✭✭✭✭✭✭
    Answer ✓

    @Archana Sahu Glad you got it to work! :)

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!