# 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

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

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

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,168,028
Messages
5,856,932
Members
431,839
Latest member
Guest user

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back