I have a workbook that lists dates of training qualifications. The individual
cells use a conditional format for expiration, 30 and 60 days before
expiration. at the bottom of each column I would like to display the
percentage of current up to date training.
The conditinal formatting works and is as follows:

Cond. #1 or = =NOW()=31 No bacground color (training is
not expired)

Cond. #2 Between =NOW()=31 and =NOW()=30 Background
Yellow (30 days to expiration)

Cond. #3 < =NOW() Background Red (training expired)


What I need is at the bottom of the column of dates. I need a formula that
will return the percentage of cells that are current. Can it be done and if
so, how?

womblew wrote:
I have a workbook that lists dates of training qualifications. The individual
cells use a conditional format for expiration, 30 and 60 days before
expiration. at the bottom of each column I would like to display the
percentage of current up to date training.
The conditinal formatting works and is as follows:

Cond. #1 or = =NOW()=31 No bacground color (training is
not expired)

Cond. #2 Between =NOW()=31 and =NOW()=30 Background
Yellow (30 days to expiration)

Cond. #3 < =NOW() Background Red (training expired)


What I need is at the bottom of the column of dates. I need a formula that
will return the percentage of cells that are current. Can it be done and if
so, how?


Suppose your dates are in B2:B52. Use this formula somewhere convenient:

=COUNTIF(B2:B52,"<"&NOW())/COUNT(B2:B52)

or perhaps more accurate if the date = today:

=COUNTIF(B2:B52,"<"&TODAY())/COUNT(B2:B52)

Did not work. Regardless of the number of cells that were input as current,
the results were 50%. The formula needs to return the percentage of cells in
the specified range that meet the criteria of cond#1.

"smartin" wrote:

womblew wrote:
I have a workbook that lists dates of training qualifications. The individual
cells use a conditional format for expiration, 30 and 60 days before
expiration. at the bottom of each column I would like to display the
percentage of current up to date training.
The conditinal formatting works and is as follows:

Cond. #1 or = =NOW()=31 No bacground color (training is
not expired)

Cond. #2 Between =NOW()=31 and =NOW()=30 Background
Yellow (30 days to expiration)

Cond. #3 < =NOW() Background Red (training expired)


What I need is at the bottom of the column of dates. I need a formula that
will return the percentage of cells that are current. Can it be done and if
so, how?


Suppose your dates are in B2:B52. Use this formula somewhere convenient:

=COUNTIF(B2:B52,"<"&NOW())/COUNT(B2:B52)

or perhaps more accurate if the date = today:

=COUNTIF(B2:B52,"<"&TODAY())/COUNT(B2:B52)

Let's talk about that condition:

Cond. #1 or = =NOW()=31

To me this says "exp.date = FALSE", which will always be true. This is
because NOW() will never be 31. Cond. 2 similarly, makes no sense to me.
Is this really what you have in your CF?

I was trying to read into your business requirement, but I probably
didn't get it right.

womblew wrote:
Did not work. Regardless of the number of cells that were input as current,
the results were 50%. The formula needs to return the percentage of cells in
the specified range that meet the criteria of cond#1.

"smartin" wrote:

womblew wrote:
I have a workbook that lists dates of training qualifications. The individual
cells use a conditional format for expiration, 30 and 60 days before
expiration. at the bottom of each column I would like to display the
percentage of current up to date training.
The conditinal formatting works and is as follows:

Cond. #1 or = =NOW()=31 No bacground color (training is
not expired)

Cond. #2 Between =NOW()=31 and =NOW()=30 Background
Yellow (30 days to expiration)

Cond. #3 < =NOW() Background Red (training expired)


What I need is at the bottom of the column of dates. I need a formula that
will return the percentage of cells that are current. Can it be done and if
so, how?

Suppose your dates are in B2:B52. Use this formula somewhere convenient:

=COUNTIF(B2:B52,"<"&NOW())/COUNT(B2:B52)

or perhaps more accurate if the date = today:

=COUNTIF(B2:B52,"<"&TODAY())/COUNT(B2:B52)

I'm sorry, I typed it wrong. This is the correct conditional formatting. The
"=" ater NOW is supposed to be a +. See below.

Cond. #1 or = =NOW()+31 No background color (training is
not expired)

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

Cond. #3 < =NOW() Background Red (training expired)


I hope this helps.

"smartin" wrote:

Let's talk about that condition:

Cond. #1 or = =NOW()=31

To me this says "exp.date = FALSE", which will always be true. This is
because NOW() will never be 31. Cond. 2 similarly, makes no sense to me.
Is this really what you have in your CF?

I was trying to read into your business requirement, but I probably
didn't get it right.

womblew wrote:
Did not work. Regardless of the number of cells that were input as current,
the results were 50%. The formula needs to return the percentage of cells in
the specified range that meet the criteria of cond#1.

"smartin" wrote:

womblew wrote:
I have a workbook that lists dates of training qualifications. The individual
cells use a conditional format for expiration, 30 and 60 days before
expiration. at the bottom of each column I would like to display the
percentage of current up to date training.
The conditinal formatting works and is as follows:

Cond. #1 or = =NOW()=31 No bacground color (training is
not expired)

Cond. #2 Between =NOW()=31 and =NOW()=30 Background
Yellow (30 days to expiration)

Cond. #3 < =NOW() Background Red (training expired)


What I need is at the bottom of the column of dates. I need a formula that
will return the percentage of cells that are current. Can it be done and if
so, how?

Suppose your dates are in B2:B52. Use this formula somewhere convenient:

=COUNTIF(B2:B52,"<"&NOW())/COUNT(B2:B52)

or perhaps more accurate if the date = today:

=COUNTIF(B2:B52,"<"&TODAY())/COUNT(B2:B52)

Here is what the column looks like. The blanks are actually red or yellow.
The dates show as clear.

14-Jan-10
26-Jan-10
14-Jan-10



14-Jan-10

14-Jan-10

14-Jan-10
16-Jan-10

14-Jan-10

28-Jan-10

26-Jan-10
14-Jan-10
14-Jan-10

14-Jan-10


14-Jan-10

26-Jan-10
14-Jan-10


14-Jan-10

14-Jan-10

14-Jan-10

"smartin" wrote:

Let's talk about that condition:

Cond. #1 or = =NOW()=31

To me this says "exp.date = FALSE", which will always be true. This is
because NOW() will never be 31. Cond. 2 similarly, makes no sense to me.
Is this really what you have in your CF?

I was trying to read into your business requirement, but I probably
didn't get it right.

womblew wrote:
Did not work. Regardless of the number of cells that were input as current,
the results were 50%. The formula needs to return the percentage of cells in
the specified range that meet the criteria of cond#1.

"smartin" wrote:

womblew wrote:
I have a workbook that lists dates of training qualifications. The individual
cells use a conditional format for expiration, 30 and 60 days before
expiration. at the bottom of each column I would like to display the
percentage of current up to date training.
The conditinal formatting works and is as follows:

Cond. #1 or = =NOW()=31 No bacground color (training is
not expired)

Cond. #2 Between =NOW()=31 and =NOW()=30 Background
Yellow (30 days to expiration)

Cond. #3 < =NOW() Background Red (training expired)


What I need is at the bottom of the column of dates. I need a formula that
will return the percentage of cells that are current. Can it be done and if
so, how?

Suppose your dates are in B2:B52. Use this formula somewhere convenient:

=COUNTIF(B2:B52,"<"&NOW())/COUNT(B2:B52)

or perhaps more accurate if the date = today:

=COUNTIF(B2:B52,"<"&TODAY())/COUNT(B2:B52)