accumulate comp time for employees

[Thaddeus]

Ok, This is what I do now:
On a sheet, I tally hours per day worked in individual
cells and total thos cells into C17 for the total hours
for the week. Following that I have the formulas for
taxes and O/T etc. to arrive at a net pay per employee.
What I would like to do:
create a "comp time" cell. My desire is this,

C17 is the total hours/week,
I want to make D17 the amount of hours the emp. would
like to go to comp time.
Then I want E17 to be the cumulateing total.

My theory--C17 has "60hrs", I enter "8" in D17 and hit
enter, "8" in D17 disappears and C17 now says "52hrs" and
E17 now says "8hrs". The following week has a total
of "58hrs" in C17 and the employee wants "5hrs" added to
comp time, an entry of "5hrs" is made in D17 and C17 now
says "53hrs" and E17 says "13hrs".

Can this be done?
Thanks, Thaddeus

 

[Frank Kabel]

Hi
this would be a little bit difficult and can only be achieved with VBA
8using event macros). Also you should consider the following drawbacks:
- you loose your audit trail
- how do you want to correct wrong entries

Also I'm not sure how you want to get the next week hours in C17 as
there's already an entry. So IMHO I wouldn't use this approach.

 

[Bob Phillips]

Hi Thaddeus,

Here is some worksheet event code to do it. To enter it, right-click on the
sheet tab, select View Code, and paste the code

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("D17")) Is Nothing Then
With Target
.Offset(0, -1).Value = .Offset(0, -1).Value - .Value
.Offset(0, 1).Value = .Value
.Value = ""
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub



--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Frank Kabel]

Hi Bob
if i understood the OP correctly I would change the line
..Offset(0, 1).Value = .Value
to
..Offset(0, 1).Value = .Offset(0, 1).Value + .Value

 

[Bob Phillips]

Hi Frank,

Good spot. Thanks.

Bob

Hi Bob
if i understood the OP correctly I would change the line
.Offset(0, 1).Value = .Value
to
.Offset(0, 1).Value = .Offset(0, 1).Value + .Value