How can I calculate Vacation Time earned based on length of emplo.

[Guest]

I have an excel worksheet where I need to list the following:

Date:
Name Hire Date Vacation Earned Days Taken Days
Remaining
Jeff 02/01/2001 10 (days) 3
7

This is the vacation calculation breakdown that I would like to calculate
automatically when I change the days taken. It will calculate lenght of
employment and how much vacation that alllows and updates days remaining
:
First year - 5 days
2-6 yrs - 10 days
7-14 yrs - 15 days
15+ yrs - 20 days

I want to be able to change days taken and have a formula calculate the rest

 

[Guest]

Hi

To calculate leave earned, use the following in C:

=IF(TODAY()-B2>15*365.25,20,IF(TODAY()-B2>6*365.25,15,IF(TODAY()-B2>2*365.25,10,5)))

To calculate remainder use the following in E:

=C2-D2

 

[X_HOBBES]

Assuming the following:
- Column A = Name
- Column B = Hire Date
- Column C = Vacation Earned
- Column D = Days Taken
- Column E = Days Remaining
- Data starts on Row 2

Formulas are as follows:
- Vacation Earned (C2):
=IF(B2<>"",IF(((NOW()-B2)/365.25)>=15,20,IF(((NOW()-B2)/365.25)>=7,15,IF(((NOW()-B2)/365.25)>=2,10,0))),"")
- Days Remaining (E2): =C2-D2

Here's a quick breakdown of the formula... It's actually a bunch of
smaller formulas grouped into one large formula.

The formula to calculate the number of days worked is: NOW()-B2

Divide that by 365.25 days per year (accounting for leap year - kind
of) and you get this formula: (NOW()-B2)/365.25

Then there are a bunch of nested IF statements to see if the person has
been there 15 years or longer ( >=15 ), 7 years or longer ( >=7 ), or 2
years or longer ( >=2 ).

I hope this helps!

X_HOBBES