Vacation Leave Summary

Basketcase

New Member
Joined
Mar 26, 2011
Messages
3
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
 

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

jim may

Well-known Member
Joined
Jul 4, 2004
Messages
7,471
A bit of Nested Ifs - IS one way: Copy C2 down
Excel Workbook
ABCDEF
1NameHire DateDays Earned
2Jim2/15/198015
3Peter9/15/20071015 days for FTemployees who have worked 4 or more years.
4Tom8/15/2009510 days for FT employees who have worked 2 yrs but less than 4 years.
5Carol3/22/201105 days for FT employees who have worked 1 year but less than 2 years.
Sheet1
Excel 2007
Cell Formulas
RangeFormula
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

Board Regular
Joined
Oct 29, 2010
Messages
220
Another one:

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

MrBill11

Board Regular
Joined
Mar 21, 2011
Messages
119

ADVERTISEMENT

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

Board Regular
Joined
Mar 21, 2011
Messages
119
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

Well-known Member
Joined
Jun 17, 2005
Messages
7,764
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,816
Messages
5,525,061
Members
409,617
Latest member
Lenaf

This Week's Hot Topics

Top