How to calculate days past due based on "text" in another column

Scott Peloquin

I need help creating the formula to calculate the number of days an item is past due based on my Status column. The Status column contains the drop-down selection of “Complete”, “Not Started”, and “In Progress”. I want the calculation to return a number whenever “Complete” is NOT selected. I’ve seen variations on this type of question based on using a Complete column and a check box, but I don’t want to have to update two columns when one will suffice to identify if something is complete or not.

Paul Newcome

I think your particular situation is going to be different from the ones you mentioned regarding checking a box. You are going to want to use something more along the lines of

=IF(Status@row <> "Complete", [First Date Column]@row - [Second Date Column]@row)

Paul Newcome

That works for display purposes, but if you wanted to run further metrics such as averaging you would need to include in your formula(s) to not use anything less than zero.

To remove negative numbers completely, you could use another IF statement to say that if the number is greater than or equal to zero then display it, otherwise leave it blank.

IF(TODAY() - [Due Date]@row >= 0, TODAY() - [Due Date]@row)

Then drop that into your output for the IF looking at the Status and you should be set.

=IF(Status@row <> "Complete", IF(TODAY() - [Due Date]@row >= 0, TODAY() - [Due Date]@row))

Paul Newcome

You are actually going to want an AND function. If you use an OR function then you are saying

If it is not "this" or not "that"

Well "this" is definitely not "that", so it will trigger a true. Give this one a whirl...

=IF(AND(Status7 <> "Complete", Status7 <> "Excluded", Status7 <> "Deferred"), IF(TODAY() - [Due Date]7 >= 0, TODAY() - [Due Date]7))

Paul Newcome

I think your particular situation is going to be different from the ones you mentioned regarding checking a box. You are going to want to use something more along the lines of

=IF(Status@row <> "Complete", [First Date Column]@row - [Second Date Column]@row)

Scott Peloquin

Thank you Paul. I made a slight adjustment and ended up with =IF(Status@row <> "Complete", TODAY() - [Due Date]@row), then I put in a Conditional Format so that when the number was <1 the font is made white so that the negative numbers are not visible. I'm not sure what to do in the formula to keep negative numbers from being displayed so that was my work around. If you have a better idea let me know. Thanks again.

Paul Newcome

That works for display purposes, but if you wanted to run further metrics such as averaging you would need to include in your formula(s) to not use anything less than zero.

To remove negative numbers completely, you could use another IF statement to say that if the number is greater than or equal to zero then display it, otherwise leave it blank.

IF(TODAY() - [Due Date]@row >= 0, TODAY() - [Due Date]@row)

Then drop that into your output for the IF looking at the Status and you should be set.

=IF(Status@row <> "Complete", IF(TODAY() - [Due Date]@row >= 0, TODAY() - [Due Date]@row))

Scott Peloquin

That worked great! Thanks. Now I can perform a metric to count the number of line items past due based on that column =COUNTIF([Days Past Due]:[Days Past Due], >=1)

Thank you.

Paul Newcome

Your COUNTIF would also work with your solution of including the negative numbers and "hiding" them since the criteria for your COUNTIF is greater than or equal to zero.

There are a number of variations you can use (as you can see). The best one will depend on your overall goal. Sometimes it is easier to make the source sheet a little more complex so that the metrics formulas can be a little more simple. Other times it won't make a huge difference either way.

Scott Peloquin

Hi Paul,

New wrinkle to this same issue. I now need to add more exclusion text to the formula. I want to show days past due if the text in the cell is NOT "Complete", "Excluded", or "Deferred". This is my current formula: =IF(Status7 <> "Complete", IF(TODAY() - [Due Date]7 >= 0, TODAY() - [Due Date]7)). How do I add the terms "Excluded" and "Deferred" after "Completed"? I tried to use the OR syntax but couldn't get it to work correctly.

Paul Newcome

You are actually going to want an AND function. If you use an OR function then you are saying

If it is not "this" or not "that"

Well "this" is definitely not "that", so it will trigger a true. Give this one a whirl...

=IF(AND(Status7 <> "Complete", Status7 <> "Excluded", Status7 <> "Deferred"), IF(TODAY() - [Due Date]7 >= 0, TODAY() - [Due Date]7))

Scott Peloquin

Worked like a charm. Thank you.

Paul Newcome

Happy to help. 👍️