count the dates past their current date

System

This discussion was created from comments split from: COUNTIF & 'not equal to'.

Safety Steve

Is it possible to count the dates in one column of four / five specific columns, which are past their current date i.e.<Today? @row.

I would be grateful for any guidance in this matter.

Regards,

Safety Steve

Paul Newcome

@Safety Steve Are you able to provide more details? Are you wanting to look through a specific column, or are you wanting to evaluate all 5 columns? Would the count be in the same sheet or a separate sheet? Are all of the columns next to each other?

Safety Steve

Contr' Compliance.jpg

Above is an attachment of a contractor compliance register.

I am trying to calculate number of expired document dates at row for that entity.

Currently I manually complete this review monthly on "Compliance Documents Missing"

I should be able to work a formula out for "Compliance Documents Listed on system"

Hoping that this can be formulated to work.

I appreciate your help in advance.

Regards,

Steve

Safety Steve

Please Note other aspects are counted hence total docs are different than shown in attachment.

Steve

Paul Newcome

From the looks of things, you are going to need a series of IF/AND combos added together

=IF(AND(ISDATE([1st Date Column]@row), [1st Date Column]@row< TODAY()), 1, 0) + IF(AND(ISDATE([2nd Date Column]@row), [2nd Date Column]@row< TODAY()), 1, 0) + IF(AND(ISDATE([3rd Date Column]@row), [3rd Date Column]@row< TODAY()), 1, 0)