Calculating Overdue days
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
-
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
-
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
-
Many thanks @Protonsponge that's exactly I was looking for.
-
@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.
-
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))
- The first part asks IF the status is "Completed" OR "Cancelled" then "0".
- 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)
- 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
-
Awesome @Protonsponge, Yes that's very helpful and correct formula. 😄
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.6K Get Help
- 403 Global Discussions
- 215 Industry Talk
- 455 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 56 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!