Accrued Vacation
 

Hi,

I need help creating a formula to calculate accrued vacation time. Employees earn either 1, 1.5, or 2 days of vacation time on their anniversatry date each month. How can I create a forumla that will automatically compute everyone's vacation time?

Thanks in advance for your help!

--
Message posted via http://www.officekb.com

Hi

what is the basis of whether they get 1, 1.5 or 2 days?

Cheers
JulieD

"Kim Campbell via OfficeKB.com" wrote in message
...
Hi,

I need help creating a formula to calculate accrued vacation time.
Employees earn either 1, 1.5, or 2 days of vacation time on their
anniversatry date each month. How can I create a forumla that will
automatically compute everyone's vacation time?

Thanks in advance for your help!

--
Message posted via http://www.officekb.com

I think the easiest way would be to create a table to use with VLOOKUP that
has the required length of service (in months?) in the 1st column, and the
number of weeks varaction in the 2nd, i.e. something like

0 0
6 1
12 1.5
36 2.0

assuming they get no vacation until they have completed 6 months, then 1
month, increasing to 1.5 after 12 months, and to 2 after 36 months.

Then if you have the date of hire in, say, column B, and the above table is in
cells K1:L4, the formula for vacation is

=VLOOKUP(B2,$K$1:$L$4,2)


On Mon, 10 Jan 2005 16:05:15 GMT, "Kim Campbell via OfficeKB.com"
wrote:

Hi,

I need help creating a formula to calculate accrued vacation time. Employees
earn either 1, 1.5, or 2 days of vacation time on their anniversatry date each
month. How can I create a forumla that will automatically compute everyone's
vacation time?

Thanks in advance for your help!

Oops. That formula should be

=VLOOKUP(DATEDIF(B2,TODAY(),"m"),$K$1:$L$4,2)



On Mon, 10 Jan 2005 12:48:55 -0600, Myrna Larson
wrote:

I think the easiest way would be to create a table to use with VLOOKUP that
has the required length of service (in months?) in the 1st column, and the
number of weeks varaction in the 2nd, i.e. something like

0 0
6 1
12 1.5
36 2.0

assuming they get no vacation until they have completed 6 months, then 1
month, increasing to 1.5 after 12 months, and to 2 after 36 months.

Then if you have the date of hire in, say, column B, and the above table is
in
cells K1:L4, the formula for vacation is

=VLOOKUP(B2,$K$1:$L$4,2)


On Mon, 10 Jan 2005 16:05:15 GMT, "Kim Campbell via OfficeKB.com"
wrote:

Hi,

I need help creating a formula to calculate accrued vacation time.
Employees
earn either 1, 1.5, or 2 days of vacation time on their anniversatry date
each
month. How can I create a forumla that will automatically compute everyone's
vacation time?

Thanks in advance for your help!

Hi Kim

did Myrna's answer give you what you're looking for?

Cheers
JulieD

"Kim Campbell via OfficeKB.com" wrote in message
...
It depends on how long they've been with the university.

--
Message posted via http://www.officekb.com

It depends on how long they've been with the university.

--
Message posted via http://www.officekb.com

Here is an example of the spreadsheet. Maybe it will help explain what I'm looking for better:


Start Date # of Days Earned Per Month Balance Type of Time
9/21/1987 2 9.75 Vacation 1 80 Sick


So in this example, the employee earns 2 vacation days on the 21st of every month. I need a forumula which will add 2 vacation days to the balance column on the 21st of every month.

Since I'm trying to bring my department into the computer world, this is just a template, if you think a formula could easily be created from another set-up, I'd be willing to make adjustments.

Thanks again for everyone's help!

--
Message posted via http://www.officekb.com

Unfortunately not exactly, so I posted an example of the spreadsheet setup.

--
Message posted via http://www.officekb.com

Hi Kim

one problem i can see with adding 2 days to the figure in the balance column
on the 21st of each month is what happens if the workbook isn't opened on
the 21st ...

how about a system where you can enter a date (or use the current date)
which will show how much vacation time the employee has accurred from
engagement on at the top of a column that has "leave taken" listed and a
figure showing the difference? would that work? or can you think of another
way of approaching it?

Cheers
JulieD


"Kim Campbell via OfficeKB.com" wrote in message
...
Here is an example of the spreadsheet. Maybe it will help explain what
I'm looking for better:


Start Date # of Days Earned Per Month Balance Type of Time
9/21/1987 2 9.75 Vacation 1 80
Sick


So in this example, the employee earns 2 vacation days on the 21st of
every month. I need a forumula which will add 2 vacation days to the
balance column on the 21st of every month.

Since I'm trying to bring my department into the computer world, this is
just a template, if you think a formula could easily be created from
another set-up, I'd be willing to make adjustments.

Thanks again for everyone's help!

--
Message posted via http://www.officekb.com