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" <[email protected]> wrote in message
news:[email protected]...
> 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"
<[email protected]> 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
<[email protected]> 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"
><[email protected]> 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!

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

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

Hi Kim

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

Cheers
JulieD

"Kim Campbell via OfficeKB.com" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
news:[email protected]...
> 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