Vacation Time calculation... HELP!
 

Hi I'm new to creating formula in Excel and I'm trying to create a
vacation calculator for a company but it's so complicated for me so any
help would be greatly appreciated.

Here's the policy of the vacation accumulation.

Worked 1 full year, gets 7D (D=Days).

2nd year, gets 9D.
Starting 2nd year, for each day accumulated, is calculated.
For example, worked 1 year and 1M (M=Month) gets 7D + (9D /12M * 1M) =
7.75 D

3rd year gets 10D.
Starting 3rd year, vacation earned prior to 2nd year is automatically
omitted.
For example, worked 2 year and 2M gets 9D + (10D / 12M * 2M) = 10.7 D

4th year, gets 12D.
Starting 4th year, vacation earned prior to 3rd year is automatically
omitted.
For example, worked 3 year and 3M gets 10D + (12D / 12M * 3M) = 13 D

5th year, gets 15D.
Starting 5th year, vacation earned prior to 4th year is automatically
omitted.
For example, worked 4 year and 2M gets 12D + (15D / 12M * 2M) = 14.5 D

6th year, gets 15D. (Vacation Raise will not increased anymore)
Starting 6th year, vacation earned prior to 5th year is automatically
omitted.
For example, worked 5 year and 1M gets 15D + (15D / 12M * 1M) = 16.25
D

7th year, gets 15D.
Starting 7th year, vacation earned prior to 6th year is automatically
omitted.
For example, worked 6 year and 1M gets 15D + (15D / 12M * 1M) = 16.25
D

And so on…

For example, today’s date is 07/22/2006 and a employee enroll on
06/22/2004.
I can calculate that one has been working for 2 year and 1 Month, so
one gets 9.83 Days.
How do I make a formula that allows me to just input the enroll date
and I get vacation days.

Again, thanks for any input and attention.


--
brubru
------------------------------------------------------------------------
brubru's Profile: http://www.excelforum.com/member.php...o&userid=36654
View this thread: http://www.excelforum.com/showthread...hreadid=564017

Vacation Time calculation... HELP!
 

brubru,

There may be better ways but with the enrolment date in G1,

=LOOKUP(DATEDIF(G1,TODAY(),"y"),{1,2,3,4,5},{7,9,1 0,12,15})+DATEDIF(G1,TODAY(),"ym")*LOOKUP(DATEDIF( G1,TODAY(),"y"),{1,2,3,4},{0.75,0.83333333333,1,1. 25})

formatted as number or General returns all your examples.


--
HTH

Sandy
In Perth, the ancient capital of Scotland


with @tiscali.co.uk


"brubru" wrote in
message ...

Hi I'm new to creating formula in Excel and I'm trying to create a
vacation calculator for a company but it's so complicated for me so any
help would be greatly appreciated.

Here's the policy of the vacation accumulation.

Worked 1 full year, gets 7D (D=Days).

2nd year, gets 9D.
Starting 2nd year, for each day accumulated, is calculated.
For example, worked 1 year and 1M (M=Month) gets 7D + (9D /12M * 1M) =
7.75 D

3rd year gets 10D.
Starting 3rd year, vacation earned prior to 2nd year is automatically
omitted.
For example, worked 2 year and 2M gets 9D + (10D / 12M * 2M) = 10.7 D

4th year, gets 12D.
Starting 4th year, vacation earned prior to 3rd year is automatically
omitted.
For example, worked 3 year and 3M gets 10D + (12D / 12M * 3M) = 13 D

5th year, gets 15D.
Starting 5th year, vacation earned prior to 4th year is automatically
omitted.
For example, worked 4 year and 2M gets 12D + (15D / 12M * 2M) = 14.5 D

6th year, gets 15D. (Vacation Raise will not increased anymore)
Starting 6th year, vacation earned prior to 5th year is automatically
omitted.
For example, worked 5 year and 1M gets 15D + (15D / 12M * 1M) = 16.25
D

7th year, gets 15D.
Starting 7th year, vacation earned prior to 6th year is automatically
omitted.
For example, worked 6 year and 1M gets 15D + (15D / 12M * 1M) = 16.25
D

And so on.

For example, today's date is 07/22/2006 and a employee enroll on
06/22/2004.
I can calculate that one has been working for 2 year and 1 Month, so
one gets 9.83 Days.
How do I make a formula that allows me to just input the enroll date
and I get vacation days.

Again, thanks for any input and attention.


--
brubru
------------------------------------------------------------------------
brubru's Profile:
http://www.excelforum.com/member.php...o&userid=36654
View this thread: http://www.excelforum.com/showthread...hreadid=564017