I need to calculate past due items with 2 column dates

Raul Cabrera

Hi

I need to create a formula that help me to calculate the past due items but using to columns with dates. One is the the first column with the "Expected closure date". This will be the primary column to check or validate. But we have another one called "Amended closure date", that will have another date that probably will tell us that it is not an item that it is past due. Then we need to check both columns and validate if it is past due. The amended closure date could be in blank. The formula that i have at this moment just to calculate the past due with the expected closure date is this one and it is working fine, but i don't know how to include a new criteria to validate the second column "Amended closure date".

Thanks in advance for anyone help

Actual formula

=IF(COUNTIFS({What is it?}, "Risk", {Severity}, "Sev 1", {Expected Closure Date}, <TODAY()) > 0, COUNTIFS({What is it?}, "Risk", {Severity}, "Sev 1", {Expected Closure Date}, <TODAY()))

Note:

See that the formula was build to leave in blank the cell if it is 0, that is because all the data will help me to create a chart in a Dashboard

Paul Newcome

Are all three columns set to date type columns?

Paul Newcome

You will need to add a date column to the source sheet with a formula that will output the correct date to evaluate.

=IF([Amended Closure Date]@row <> "", [Amended Closure Date]@row, [Expected Closure Date]@row)

Then evaluate this one in your metrics sheet.

Raul Cabrera

Hi Paul, thanks for you answer. I did it but still is not working, as you can see in the screeshoot now is giving me this error #invalid column value

The formula is the one that you gave it to me

=IF([Amended Closure Date]@row <> "", [Amended Closure Date]@row, [Expected Closure Date]@row)

Paul Newcome

Are all three columns set to date type columns?

Raul Cabrera

Yes three columns will be dates types.

I set this formula that works now. The invalid error it was because the column was not set as date property

=IF([Amended Closure Date]@row <> "", [Amended Closure Date]@row, [Expected Closure Date]@row)

As usual thanks Paul

Paul Newcome

Happy to help. 👍️