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
 

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.

jim may

Well-known Member
Joined
Jul 4, 2004
Messages
7,484
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,772
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.
 
Master Excel Bundle

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.

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top