Calculating number of days a task is overdue

Ollie Gray

I am trying to calculate the number of days a tasks is overdue. If the task has been completed, I would like it to be blank or return a "0". Here is the formula that I have tried and it returns unparseable

=IF(AND([Finish Date]@row < TODAY(), % Complete@row = "100"), "0", IF(AND([Finish Date]@row >= TODAY(), OR(% Complete@row = "<100"), "+" + ABS(TODAY() - [Finish Date]@row), TODAY() - [Finish Date]@row))

Jason Miller

=IF(AND([% Complete]@row <> 1, [Finish Date]@row < TODAY()), TODAY() - [Finish Date]@row, "")

1. Make sure you have brackets around the % Complete if there is a space between % and Complete (in no space it does not matter: [% Complete]@row
2. If the Finish Date cell is not populated you will get #INVALID OPERATION, you can fix this by placing IFERROR( after the = sign at the beginning and ,"" after the last parenthesis (see formula below):

=IFERROR(IF(AND([%Complete]@row <> 1, [Finish Date]@row < TODAY()), TODAY() - [Finish Date]@row, ""), "")

Let me know if this does not work.

Ollie Gray

I made a change, and it is only returning a "0" even if the task is not completed

=IF(OR([Finish Date]@row < TODAY(), [% Complete]@row = "1"), "0", IF(AND([Finish Date]@row >= TODAY(), OR([% Complete]@row = "<1"), "+" + ABS(TODAY() - [Finish Date]@row), TODAY() - [Finish Date]@row)))

Ollie Gray

Still trying to get this to work Here is a screen shot of the sheet

here is the formula that is unparseable.

=IF(OR([% Complete]@row = 1), 0, IF(AND(OR([% Complete]@row = <1, ), [Due Date]@row > TODAY()), "+" + ABS(TODAY() - [Due Date]@row), TODAY() - [Due Date]@row))

Jason Miller

Double check the column type is text (just to be safe). This simplified version should work, just copy and paste this in:

=IFERROR(IF(AND([% Complete]@row <> 1, [Due Date]@row < TODAY()), TODAY() - [Due Date]@row, ""), "")

The IFERROR statement should take care of the #UNPARSEBLE error, which may be happening if % Complete is blank. You can try changing it blanks to "0" if the IFERROR formula does not fix it.

Calculating Num Days.png

Ollie Gray

it is still calculating overdue days on completed tasks