Accruing Vacation Time as Time Passes

H

Hugnuffer

Hi,

Any ideas how to get excel to add 6 hours of vacation time to total
vacation time every two weeks? Thanks.
 
D

Don

Can you please provide more details?
How does you current spreadsheet work?
What data is there currently?
Is the two weeks able to be identified by a certain calendar of dates?

Sorry. More questions for you, but without answers to those, it will be
hard for me to help.
 
H

Hugnuffer

No problem with the questions. I appreciate your help. It's a very simple
spreadsheet that I am using to track employees' vacation time. It's one
column per employee, with the total number of vacation hours at the top. As
people take time off, I enter it into the column (below the total time off)
and it is subtracted from the total. I'm trying to get Excel to update the
total time off by adding six hours to it every pay period (15th and last day
of the month). If I could make it so six hours could be added twice monthly,
that would be sufficient, without having to bother with 30th's and 31st's and
February having 28 days, if that creates a huge complication. Please feel
free to ask any additional questions. Thanks!!
 
D

Don

How are you idenifying the "current pay period"?

I set up a quick spreadsheet, as follows (this is what mine would look like
anyway)...

----------------------------------------------------------
Current pay period 3/31/2006

PayPeriod Empl1 Empl2 Empl3
Accrued 30

1/15/2006 1
1/31/2006 2 6
2/15/2006 3
2/28/2006 4
3/15/2006 5
3/31/2006 6
----------------------------------------------------------

Two things are key.

1. The current payperiod date at the top (must match the payperiods in
column A)
2. The formula in the Cell beneath Empl1, which is
=VLOOKUP(D2, $B$7:$C$33, 2, FALSE)*6-SUM(D7:D33)

The formula looks at the date at the top to see which period it is, then
compares to the list down the left, and when it finds it, it looks and grabs
the value in the "payperiod" column. It then multiplies that result by 6 (6
hours per pay period) and then subtracts the sum od the time taken in the
rows beneath for that employee (in this case, 03/31/06 = 6, so, 6*6 - 6 = 30)

Hope this helps
 
Top