I use a workbook to track training dates for employees. The conditional
formatting and is as follows:

Cond#1 or= =NOW()=31 No background color (shows non-expired dates)

Cond.#2 Between =NOW() and =NOW()=30 Background is YELLOW (shows
30 days to expiration)

Cond.#3 < =NOW() Background is RED (shows expired training dates)


I need a formula at the bottom of the columns that will display the
percentage of non-expired dates (No background)

If your dates are in A1:A100 then you can enter this in A101

=COUNTIF(A1:A100,"" & NOW())/COUNT(A1:A100)
and format as Percentage

"womblew" wrote:

I use a workbook to track training dates for employees. The conditional
formatting and is as follows:

Cond#1 or= =NOW()=31 No background color (shows non-expired dates)

Cond.#2 Between =NOW() and =NOW()=30 Background is YELLOW (shows
30 days to expiration)

Cond.#3 < =NOW() Background is RED (shows expired training dates)


I need a formula at the bottom of the columns that will display the
percentage of non-expired dates (No background)

Hi,

I'm going to guess you mean for the first condition =NOW()+31

If so your formula would be, assuming you dates are in column A:

=COUNTIF(A1:A100,"="&NOW()+31)/COUNT(A1:A100)

format as percent.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"womblew" wrote:

I use a workbook to track training dates for employees. The conditional
formatting and is as follows:

Cond#1 or= =NOW()=31 No background color (shows non-expired dates)

Cond.#2 Between =NOW() and =NOW()=30 Background is YELLOW (shows
30 days to expiration)

Cond.#3 < =NOW() Background is RED (shows expired training dates)


I need a formula at the bottom of the columns that will display the
percentage of non-expired dates (No background)

You are correct about the = sign. I have recopied the conditional formating
below to show the corrections. The formula gives me 100% even with cells
that do not have a current date. It does not take into account Cond #2 and
#3. Do I need to nest a difference formula in the first part? I'm sure how I
would do that. I thin it would have to add all three conditions of the column
and then subtract the non-current (Cond #2 and #3) prior to dividing.

Cond#1 or= =NOW()+31 No background color (shows non-expired dates)

Cond.#2 Between =NOW() and =NOW()+30 Background is YELLOW (shows
30 days to expiration)

Cond.#3 < =NOW() Background is RED (shows expired training dates)

"Sheeloo" wrote:

If your dates are in A1:A100 then you can enter this in A101

=COUNTIF(A1:A100,"" & NOW())/COUNT(A1:A100)
and format as Percentage

"womblew" wrote:

I use a workbook to track training dates for employees. The conditional
formatting and is as follows:

Cond#1 or= =NOW()=31 No background color (shows non-expired dates)

Cond.#2 Between =NOW() and =NOW()=30 Background is YELLOW (shows
30 days to expiration)

Cond.#3 < =NOW() Background is RED (shows expired training dates)


I need a formula at the bottom of the columns that will display the
percentage of non-expired dates (No background)