Calculating Overdue days

Saj

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,

Protonsponge

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

Protonsponge

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

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

Saj

@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

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

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

Protonsponge

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

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

Protonsponge