Calculate vacation eligibility dates in a certain year

[Only4Excel]

Hello Everyone,

Our vacation plan is as follows. DOH = Date of Hire

DOH+110, DOH+220, DOH+365, DOH+475, DOH+585, DOH+730 etc....

means 1st vacation 110 days after DOH, 2nd after DOH+110+110, 3rd after DOH+110+110+145, 4th one after DOH+110+110+145+110 etc so basically 3 times in 365 days.

My question how can I calculate the vacation eligibility dates in a specific year & it's position number?

eg: if i want to know the dates in 2012 1st one is 4/19/2012, 2nd 8/7/2012, 3rd one is on 12/30/2012

I got this to work with helper cells.

A1 = DOH

B2:B30 to down, 110, 110, 145, 110, 110, 145, 110, 110, 145 etc...the in A2:A30 to down =A1+B2

E1 is the date 1/1/2012

1st vacation in D3 =INDEX(A2:A30,MATCH(E1,A2:A30)+(LOOKUP(E1,A2:A30)<E1))
2nd n D4: =INDEX(A$2:A$30,MATCH(D3,A$2:A$30,0)+1)
3rd in D5 : =INDEX(A$2:A$30,MATCH(D4,A$2:A$30,0)+1)

And it's psition number is MATCH dates in A2:A30, which 22, 23, 24 respectively.

How can I do this wihout helper cells?

Thank you fr your time

Raj

[Dennis7849]

When you say "1st vacation" do you mean the first day(8 hours) of vacation time is accumulated?

This can be done using macros.

[Only4Excel]

Thank you for your reply Dennis,

1st vacation means after 110 days since hired. Doesn't matter about how many hours worked in these period, other leaves (ie other vacation, if you look for 2nd vacation 1st vacation days) are not matter. Just 3 times with in 365 days. 110, 110 & 145

[daddylonglegs]

Hello Raj,

Try this to avoid using helper columns, assuming DOH in A1 and a later date like 1/1/2012 in E1

Initially get the first position in E3

=SUMPRODUCT((E1>A1+{110,220,365}+365*(ROW(INDIRECT("1:50"))-1))+0)+1

then in E4 copied down

=E3+1

Now you can get the dates in D3 with this formula copied down

=SMALL(INDEX(A$1+{110,220,365}+365*(ROW(INDIRECT("1:50"))-1),0),E3)

[Only4Excel]

Daddylonglegs,

Awesome.......... no words.. Thank you so much......

[fizataylor]

Can some one please help.

I need to calculate vacation year to date.

1st year is waiting
2nd - 6th year - 5 days
7th - 9th - 10 days
10th + - 15 days

I need the
total hrs for the employee has accrued for all the years.

Date of hire - 1/15/06

I am looking for the formula which employee has accrued on 1st year, 2nd -6th year, 7th - 9th , and 10 + = Total hrs employee accrued.

Thanks,

Fiza