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
-
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 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 Glad you got it to work! :)
Answers
-
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
-
@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.
-
-
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
-
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
-
@Archana Sahu Glad you got it to work! :)
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 349 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!