Colomn of various dates, count & give % of all under 1 year, etc.
 

I am trying to work out a formula(s) that, on a daily basis identifies the
number of dates that are within 1 year (or 2 or 3 as desired), total, and
give a percentage overall. Those that do not conform within the time frame
have a conditional formatting to change colour (I can do this).
I am using this for a training qualification chart, to identify dates that
become unqualifed, and require the training again. This is also to assist in
corperate reporting, etc.

Colomn of various dates, count & give % of all under 1 year, etc.
 

I think this should do it for you.

=SUMPRODUCT(--(DATEDIF(A1:A5,TODAY(),"y")<1))/COUNT(A1:A5)

A1:A5 is your range of dates.

This counts the number of dates that are less than 1 full year from
today's date and divides it by the total number of dates. Format your
cell as percentage.

Does that help?

Steve


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=515033

Colomn of various dates, count & give % of all under 1 year, e
 

Thank you SteveG.

This formula certainly gave me foundation to utilize a flexible dating
system. With some experimentation, I went one step farther and used part of
the formula to "count" dates within a certain time frame.
I am not sure how the "y" works in this formula, however the results for:
=SUMPRODUCT(--(DATEDIF(A1:A5,TODAY(),"y")<1))
worked great. I modied the cell varibles to read:
=SUMPRODUCT(--(DATEDIF(L58:L95,TODAY(),"y")<L106))
and changed the "1" (year) for a cell reference (L106) to which I can input
1, 2, 3 (number of years) or 99 (situations were one date is acceptable).

This chart allows me to "date" when an individual completes a qualification
course. In this case, I have all the dates "conditional formated" and change
"RED" when they are "out of date". Problem was my count and percentage would
not reflect the changes. Now, with Steves help, the chart only counts the
dates within the criteria and with an added cell I have it giving the
percentage as well.

Thank you again,
Thomas

"SteveG" wrote:


I think this should do it for you.

/COUNT(A1:A5)

A1:A5 is your range of dates.

This counts the number of dates that are less than 1 full year from
today's date and divides it by the total number of dates. Format your
cell as percentage.

Does that help?

Steve


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=515033

Colomn of various dates, count & give % of all under 1 year, etc.
 

Thomas,

Glad I was able to help.

Cheers,

Steve


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=515033