Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Kim
 
Posts: n/a
Default How can I calculate Vacation Time earned based on length of emplo.

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
  #2   Report Post  
Kassie
 
Posts: n/a
Default

Hi

To calculate leave earned, use the following in C:

=IF(TODAY()-B215*365.25,20,IF(TODAY()-B26*365.25,15,IF(TODAY()-B22*365.25,10,5)))

To calculate remainder use the following in E:

=C2-D2

"Kim" wrote:

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

  #3   Report Post  
X_HOBBES
 
Posts: n/a
Default

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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Calculate total interest earned on a loan and monthly payment Matt Stanley Excel Worksheet Functions 4 May 4th 23 03:42 AM
Calculate time difference to the half hour Ken Ivins Excel Worksheet Functions 6 July 17th 05 05:48 PM
How do I calculate charges based on elasped time(H:MM) & rate($)? glass-artist-web-developer Excel Discussion (Misc queries) 1 March 12th 05 03:09 AM
calculate difference in time to hours Chris Excel Worksheet Functions 5 January 18th 05 07:07 PM
calculate negative or positve difference in time kpmoore Excel Discussion (Misc queries) 2 January 5th 05 02:35 AM


All times are GMT +1. The time now is 08:04 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"