# Vacation Leave Summary

##### New Member
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

### 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.

#### njimack

##### Well-known Member
Excel Workbook
ABCD
1EmployeeStart DateYrsDays
2A01/01/2007415
3B01/01/2008310
4C01/01/2009210
Sheet1

#### jim may

##### Well-known Member
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
Another one:

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

#### MrBill11

##### Board Regular

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
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
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.

Replies
1
Views
72
Replies
7
Views
83
Replies
6
Views
110
Replies
1
Views
80
Replies
2
Views
53