using a year in a date
 

I have a list of employees and have calculated their retirement dates. this
among other things are calculated on sheet 1 (labeled senioritylist). On
sheet 2 I want to calculate the percentage of employees that have reached
their retirement date or are within 5 years of retirement, and then do the
same in the 5-10 year retirment group, etc. I was trying to use
=countif(senioritylist!k3:k58,"(today()+365.25*5)" ) but that does not work.
What am I doing wrong?
--
andy

using a year in a date
 

Actually you were very close:

=COUNTIF(senioritylist!K3:K58,"<=" & (TODAY()+365.25*5))

is a "reasonable" estimate.
--
Gary''s Student - gsnu200844


"andy" wrote:

I have a list of employees and have calculated their retirement dates. this
among other things are calculated on sheet 1 (labeled senioritylist). On
sheet 2 I want to calculate the percentage of employees that have reached
their retirement date or are within 5 years of retirement, and then do the
same in the 5-10 year retirment group, etc. I was trying to use
=countif(senioritylist!k3:k58,"(today()+365.25*5)" ) but that does not work.
What am I doing wrong?
--
andy

using a year in a date
 

If you want to check the dates falling before today + five years use
=COUNTIF(senioritylist!K3:K58,"<" & (TODAY()+365.25*5))

also will give you dates falling AFTER 5 year

I prefer to use
=DATE(YEAR(TODAY())+5,MONTH(TODAY()),DAY(TODAY()))
to add 5 years

-------------------------------------
Pl. click ''''Yes'''' if this was helpful...



"andy" wrote:

I have a list of employees and have calculated their retirement dates. this
among other things are calculated on sheet 1 (labeled senioritylist). On
sheet 2 I want to calculate the percentage of employees that have reached
their retirement date or are within 5 years of retirement, and then do the
same in the 5-10 year retirment group, etc. I was trying to use
=countif(senioritylist!k3:k58,"(today()+365.25*5)" ) but that does not work.
What am I doing wrong?
--
andy

using a year in a date
 

thank you so very much "Gary"s Student" I mulled that over all day at work.
--
andy


"Gary''s Student" wrote:

Actually you were very close:

=COUNTIF(senioritylist!K3:K58,"<=" & (TODAY()+365.25*5))

is a "reasonable" estimate.
--
Gary''s Student - gsnu200844


"andy" wrote:

I have a list of employees and have calculated their retirement dates. this
among other things are calculated on sheet 1 (labeled senioritylist). On
sheet 2 I want to calculate the percentage of employees that have reached
their retirement date or are within 5 years of retirement, and then do the
same in the 5-10 year retirment group, etc. I was trying to use
=countif(senioritylist!k3:k58,"(today()+365.25*5)" ) but that does not work.
What am I doing wrong?
--
andy

using a year in a date
 

In L3, put:

=DATEDIF(K3,TODAY(),"y")
and copy down

In K60, put:
=COUNTIF(L3:L58,"=5")


--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"andy" wrote in message
...
I have a list of employees and have calculated their retirement dates.
this
among other things are calculated on sheet 1 (labeled senioritylist). On
sheet 2 I want to calculate the percentage of employees that have reached
their retirement date or are within 5 years of retirement, and then do the
same in the 5-10 year retirment group, etc. I was trying to use
=countif(senioritylist!k3:k58,"(today()+365.25*5)" ) but that does not
work.
What am I doing wrong?
--
andy

using a year in a date
 

Ok, I took my stupid pills today. Using your formula, I tried to change the
5 year to 10 years and subtract out the 5 year retirees. It doesn't work. I
tried
=COUNTIF(SENIORITYLIST!K3:K58,"<="&(TODAY()+365.25 *10))-COUNTIF(senioritylist!K3:K58,"<=" & (TODAY()+365.25*5)) What do you think?
--
andy


"Gary''s Student" wrote:

Actually you were very close:

=COUNTIF(senioritylist!K3:K58,"<=" & (TODAY()+365.25*5))

is a "reasonable" estimate.
--
Gary''s Student - gsnu200844


"andy" wrote:

I have a list of employees and have calculated their retirement dates. this
among other things are calculated on sheet 1 (labeled senioritylist). On
sheet 2 I want to calculate the percentage of employees that have reached
their retirement date or are within 5 years of retirement, and then do the
same in the 5-10 year retirment group, etc. I was trying to use
=countif(senioritylist!k3:k58,"(today()+365.25*5)" ) but that does not work.
What am I doing wrong?
--
andy

using a year in a date
 

Is the answer at least close??
--
Gary''s Student - gsnu200844


"andy" wrote:

Ok, I took my stupid pills today. Using your formula, I tried to change the
5 year to 10 years and subtract out the 5 year retirees. It doesn't work. I
tried
=COUNTIF(SENIORITYLIST!K3:K58,"<="&(TODAY()+365.25 *10))-COUNTIF(senioritylist!K3:K58,"<=" & (TODAY()+365.25*5)) What do you think?
--
andy


"Gary''s Student" wrote:

Actually you were very close:

=COUNTIF(senioritylist!K3:K58,"<=" & (TODAY()+365.25*5))

is a "reasonable" estimate.
--
Gary''s Student - gsnu200844


"andy" wrote:

I have a list of employees and have calculated their retirement dates. this
among other things are calculated on sheet 1 (labeled senioritylist). On
sheet 2 I want to calculate the percentage of employees that have reached
their retirement date or are within 5 years of retirement, and then do the
same in the 5-10 year retirment group, etc. I was trying to use
=countif(senioritylist!k3:k58,"(today()+365.25*5)" ) but that does not work.
What am I doing wrong?
--
andy

using a year in a date
 

yeah, your original formula worked to figure out how many were within 5 years
of retirement, but when I try the expanded formula to try and find out how
many within 10 years of retirement and then subtract out the 5 years. I get
an error to the formula.
--
andy


"Gary''s Student" wrote:

Is the answer at least close??
--
Gary''s Student - gsnu200844


"andy" wrote:

Ok, I took my stupid pills today. Using your formula, I tried to change the
5 year to 10 years and subtract out the 5 year retirees. It doesn't work. I
tried
=COUNTIF(SENIORITYLIST!K3:K58,"<="&(TODAY()+365.25 *10))-COUNTIF(senioritylist!K3:K58,"<=" & (TODAY()+365.25*5)) What do you think?
--
andy


"Gary''s Student" wrote:

Actually you were very close:

=COUNTIF(senioritylist!K3:K58,"<=" & (TODAY()+365.25*5))

is a "reasonable" estimate.
--
Gary''s Student - gsnu200844


"andy" wrote:

I have a list of employees and have calculated their retirement dates. this
among other things are calculated on sheet 1 (labeled senioritylist). On
sheet 2 I want to calculate the percentage of employees that have reached
their retirement date or are within 5 years of retirement, and then do the
same in the 5-10 year retirment group, etc. I was trying to use
=countif(senioritylist!k3:k58,"(today()+365.25*5)" ) but that does not work.
What am I doing wrong?
--
andy