Vacation Leave Summary

[Basketcase]

Need help with calculating the total number of vacation days and days remaining for each vacation plan. Can anyone help me with a formula for this?
Vacation Data:

15 days for FTemployees who have worked 4 or more years.
10 days for FT employees who have worked 2 yrs but less than 4 years.
5 days for FT employees who have worked 1 year but less than 2 years.

Thanks

 

[njimack]

Excel Workbook
	A	B	C	D
1	Employee	Start Date	Yrs	Days
2	A	01/01/2007	4	15
3	B	01/01/2008	3	10
4	C	01/01/2009	2	10
Sheet1

 

[jim may]

A bit of Nested Ifs - IS one way: Copy C2 down

Excel Workbook
	A	B	C	D	E	F
1	Name	Hire Date	Days Earned
2	Jim	2/15/1980	15
3	Peter	9/15/2007	10			15 days for FTemployees who have worked 4 or more years.
4	Tom	8/15/2009	5			10 days for FT employees who have worked 2 yrs but less than 4 years.
5	Carol	3/22/2011	0			5 days for FT employees who have worked 1 year but less than 2 years.
Sheet1

Excel 2007

Cell Formulas
Range	Formula
C2	=IF(DATEDIF(B2,TODAY(),"y")>=4,15,IF(AND(DATEDIF(B2,TODAY(),"y")<4,DATEDIF(B2,TODAY(),"y")>2),10,IF(AND(DATEDIF(B2,TODAY(),"y")<2,DATEDIF(B2,TODAY(),"y")>=1),5,0)))

 

[GK039]

Another one:

=IF(YEAR(TODAY())-YEAR(A2)>=4,15,IF(YEAR(TODAY())-YEAR(A2)>=2,10,5))

 

[MrBill11]

njimack,
I am new to this, but I think your formula is missing something. If the employee has less than one year your formula would show 5 days. The formula would need to be =IF(C2>4,15,IF(C2=2,10,IF(C2=1,5,0))).

 

[MrBill11]

My formula had an error. It should be
=IF(C2>4,15,IF(C2=3,10,IF(C2=2,10,IF(C2=1,5,0))))

 

[njimack]

njimack,
I am new to this, but I think your formula is missing something. If the employee has less than one year your formula would show 5 days. The formula would need to be =IF(C2>4,15,IF(C2=2,10,IF(C2=1,5,0))).

You're right, but since the OP didn't state how many days for employees with less than 1 yr, I didn't include that condition.