| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Determine year over year date for comparison | Excel Worksheet Functions | |||
| If a date range contains a leap year (date) | Excel Worksheet Functions | |||
| Sorting a date by month, date and then year. | Excel Discussion (Misc queries) | |||
| Year-to-date year to date formula | Excel Worksheet Functions | |||
| Date formula: return Quarter and Fiscal Year of a date | Excel Discussion (Misc queries) | |||
Linear Mode
