Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculate total interest earned on a loan and monthly payment | Excel Worksheet Functions | |||
Calculate time difference to the half hour | Excel Worksheet Functions | |||
How do I calculate charges based on elasped time(H:MM) & rate($)? | Excel Discussion (Misc queries) | |||
calculate difference in time to hours | Excel Worksheet Functions | |||
calculate negative or positve difference in time | Excel Discussion (Misc queries) |