Seiniority/Vacation

bodigard

New Member
Joined
Oct 7, 2016
Messages
8
Hi, I have a formula that gives me an employees start date and with that I was able to determine their allowed vacation days based on one year of service the minimum (thanks CalcSux78).
My problem is, we hired some new employees and the chart won't populate dates less than one year.
I have a chart on sheet 2 with the following info.
YEARS(S)
DAYS OFF
1
10
3
15
9
20
18
25
25
30

<tbody>
</tbody>


The formula is:
=INDEX(Sheet2!$B$2:$B$6,MATCH(YEAR(TODAY()-I31)-1900,Sheet2!$A$2:$A$6,1),0)

The rule is as followes - 6 months= 6 days vacation, 5 months= 5 days vacation, 4 months= 4 days vacation, 3 months= 3 days vacation, 2 months= 2 days vacation and 1 month= 1 day vacation.

How could I add these new rules to the above formula?
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
Forgot the years. How about :
=IF(DATEDIF(A1, TODAY(), "m")<=6,DATEDIF(A1, TODAY(), "m"), CHOOSE(MATCH(DATEDIF(A1, TODAY(), "y"), {1;3;9;18;25}),10,15,20,25,30))
 
Upvote 0
Hi bodigard

your "less than 1 year " rule is incomplete . you state that The rule is as followes - 6 months= 6 days vacation, 5 months= 5 days vacation, 4 months= 4 days vacation, 3 months= 3 days vacation, 2 months= 2 days vacation and 1 month= 1 day vacation.

But with will you do with employees who have 7 to 11 months service ?. At the moment they will get nothing as they aren't covered by either the "at least 1 year" rule or your new rules

Here's my version

col a = names
col b = start dates
col c =YEARFRAC(B2,TODAY()) ... gives years with decimal places eg 6 month = .5 . 10 years , 6 months = 10.5

update your table to include the extra rows . I have put my table to columns in columns I to K as below (I have put my suggestion for # days that employees with 7 to 11 months should get ... change as needed) .. I have changed your lookup table to have 3 columns for purposes of clarity only

once you have this you use match and index using this formula in col D =INDEX(K$2:K$18,MATCH(C2,J$2:J$18,1))



descYEARSLEAVE DAYS
0 months00
1 month0.0833331
2 month0.1666672
3 month0.253
4 month0.3333334
5 month0.4166675
6 month0.56
7 month0.5833337
8 month0.6666677
9 month0.758
10 month0.8333338
11 month0.9166679

<tbody>
</tbody>
 
Last edited:
Upvote 0
Sorry, 6 months of employment up to 1 year remains the same 6 days.
The rules are as followes:

Start Date

Vacation Entitlement


January
February
March
April
May
June
48 hours
40 hours
32 hours
24 hours
16 hours
8 hours

*** New employees hired from July 1st to December 31st, shall not be eligible for vacation during the current vacation period.


<tbody>
</tbody>
 
Upvote 0
I have the actual chart here:

YEARS(S)DAYS OFFHOURS OFF
11080
315120
920160
1825200
2530240
MONTH(S)DAYS OFFHOURS OFF
JAN648
FEB540
MAR432
APR324
MAY216
JUN18

<tbody>
</tbody><colgroup><col span="2"><col></colgroup>
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,920
Members
448,533
Latest member
thietbibeboiwasaco

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
Back
Top