# Seiniority/Vacation

#### bodigard

##### New Member
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

If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

#### WarfritLive

##### Board Regular
If column A is the hire date, try:

=IF(DATEDIF(A1, TODAY(), "m")>=6,6DATEDIF(A1, TODAY(), "m"))

#### mikerickson

##### MrExcel MVP
Try adding a row at the top 0 years of service = 0 vacation days.

#### WarfritLive

##### Board Regular
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))

#### liveinhope

##### Well-known Member
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))

 desc YEARS LEAVE DAYS 0 months 0 0 1 month 0.083333 1 2 month 0.166667 2 3 month 0.25 3 4 month 0.333333 4 5 month 0.416667 5 6 month 0.5 6 7 month 0.583333 7 8 month 0.666667 7 9 month 0.75 8 10 month 0.833333 8 11 month 0.916667 9

<tbody>
</tbody>

Last edited:

#### bodigard

##### New Member
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>

#### bodigard

##### New Member
I have the actual chart here:

 YEARS(S) DAYS OFF HOURS OFF 1 10 80 3 15 120 9 20 160 18 25 200 25 30 240 MONTH(S) DAYS OFF HOURS OFF JAN 6 48 FEB 5 40 MAR 4 32 APR 3 24 MAY 2 16 JUN 1 8

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

Replies
1
Views
343
Replies
6
Views
1K
Replies
32
Views
2K
Replies
1
Views
2K
Replies
2
Views
445

1,191,373
Messages
5,986,271
Members
440,015
Latest member
knijgh

### 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.

### Which adblocker are you using?

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

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