Counting Red Text Colour, When Applied by Conditional Formatting

Hello,

I have a spreadsheet that is used for testing equipment. In the G column it has a list of the expiry dates for the equipment to be tested.

I've used Conditional Formatting to change the font to Orange (when it will expire in 30 days or less) and Red (when a service is overdue.)

This are the conditional formatting formulas:

=NOW()+30

&

=NOW()

What I want to do is to count all of the items that have turned red. So I can see how many will need to be tested. This is going to be a big spreadsheet so I can't just count manually and risk missing things out.

Is there anyway of achieving this, preferrably without VBA?

My sample spreadsheet wont attach for some reason so will try again later!

Any help would be amazing!

Use Countif() function

=COUNTIF(A:A,NOW()+30)

=COUNTIF(A:A,NOW())

Or

=COUNTIF(A:A,NOW()+30)+COUNTIF(A:A,NOW())

Or

To count >=now() and <=now()+30

=COUNTIFS(A:A,">="&NOW(),A:A,"C="&NOW()+30)

Hi,

I can't see how either of your CFs are working. NOW() returns a decimal number that can be interpreted as a date & time.

You would need to use

=A1=TODAY() ' for red and
=A1<TODAY+30 ' for orange

in that order if you want to work with just the date.

For your count of the red cell then simply use the same logic as the conditional format setting, i.e.
=COUNTIF(A:A,TODAY())

Sorry,

I didn't copy the conditional formatting properly

It is done as follows:

Format only cells that contain - Cell Value - Less than - =NOW()

and the same with the other, just with =NOW()+30

Does that change anything you have previously stated?

I've also just tried both formulas offered and the answer comes back as 0.

Hi,

With the two CFs I gave you you should be using the 'Formula is.....(last) option.

In addition and as I said, use TODAY() rather than NOW()

Hi,

Upload the workbook and I'll change it for you.