Accrued Vacation

[Kim Campbell via OfficeKB.com]

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!

 

[JulieD]

Hi

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

Cheers
JulieD

 

[Myrna Larson]

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)

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?

 

[Myrna Larson]

Oops. That formula should be

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

 

[JulieD]

Hi Kim

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

Cheers
JulieD

 

[Kim Campbell via OfficeKB.com]

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

 

[Kim Campbell via 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!

 

[Kim Campbell via OfficeKB.com]

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

 

[JulieD]

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