Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Seniority Years Formula needed
Does anyone know the formula for calculating a person seniority years
i tried subtracting the End Date from Start date / 365 but because of leap years this does not accurately calculate. any ideas? Hire Date Current Date =(B2-A2)/365 08/01/1908 07/23/2008 100.04 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Seniority Years Formula needed
=DATEDIF(A1,B1,"y")
-- Gary''s Student - gsnu200796 "Roibn L Taylor" wrote: Does anyone know the formula for calculating a person seniority years i tried subtracting the End Date from Start date / 365 but because of leap years this does not accurately calculate. any ideas? Hire Date Current Date =(B2-A2)/365 08/01/1908 07/23/2008 100.04 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Seniority Years Formula needed
Assumptions, hiredate and currentdate are dates only.
=IF(OR(hiredate="",currentdate=""),"",YEAR(current date)-YEAR(hiredate)) or, if you want to get a little fancier, in your example below, it should actually be 99 whole years (a few days short of 100), you could use the following formula: =IF(OR(currentdate="",hiredate=""),"",YEAR(current date)-YEAR(hiredate)-IF(DATE(YEAR(2000),MONTH(currentdate),DAY(currentd ate))<DATE(YEAR(2000),MONTH(hiredate),DAY(hiredate )),1,0)) -- John C "Roibn L Taylor" wrote: Does anyone know the formula for calculating a person seniority years i tried subtracting the End Date from Start date / 365 but because of leap years this does not accurately calculate. any ideas? Hire Date Current Date =(B2-A2)/365 08/01/1908 07/23/2008 100.04 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Seniority Years Formula needed
Try the following formula, changing the cell references to match you worksheet:
=(B1-A1)/365.254 -- Kevin Backmann "Gary''s Student" wrote: =DATEDIF(A1,B1,"y") -- Gary''s Student - gsnu200796 "Roibn L Taylor" wrote: Does anyone know the formula for calculating a person seniority years i tried subtracting the End Date from Start date / 365 but because of leap years this does not accurately calculate. any ideas? Hire Date Current Date =(B2-A2)/365 08/01/1908 07/23/2008 100.04 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Seniority Years Formula needed
Don't forget to format your result cell as general, or it will show up as a
date. -- John C "Roibn L Taylor" wrote: Does anyone know the formula for calculating a person seniority years i tried subtracting the End Date from Start date / 365 but because of leap years this does not accurately calculate. any ideas? Hire Date Current Date =(B2-A2)/365 08/01/1908 07/23/2008 100.04 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Seniority Years Formula needed
Thanks worked perfectly
"Gary''s Student" wrote: =DATEDIF(A1,B1,"y") -- Gary''s Student - gsnu200796 "Roibn L Taylor" wrote: Does anyone know the formula for calculating a person seniority years i tried subtracting the End Date from Start date / 365 but because of leap years this does not accurately calculate. any ideas? Hire Date Current Date =(B2-A2)/365 08/01/1908 07/23/2008 100.04 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Scheduling staff to tasks based on seniority | Excel Discussion (Misc queries) | |||
How to allocate based on seniority | Excel Discussion (Misc queries) | |||
How do I subtract dates to get a number in years or years & month | Excel Discussion (Misc queries) | |||
Recalc needed for successive years | Excel Discussion (Misc queries) | |||
Overlay 4 years of data as a line on 4 years of columns for several x category labels | Charts and Charting in Excel |