Can I write a formula for past due calculation?

Archana Sahu

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

Heather Duff

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

@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

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

Heather Duff

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

Archana Sahu

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

@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.

Archana Sahu

Thank you so much @Heather Duff !!!

It worked for me.

Regards,

Archana Sahu

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

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

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