Time Function

[Tony Johnson]

When emplyees call in their daily hours worked I need to take cell E2 and
add that value to cell C2 then clear cell E2s amount to be ready to accept
another value.


A B C D
E
1 Employee Time Avail Time Worked Time Remaining Time
worked Today
2 John Smith 35:00:00 1:05:00 33:55:00


Cell C2 need to keep a running total
Also need a way to quickly reset all values in column C to no hours

Thanks, you help is greatly appreciated.

 

[Bob Phillips]

Hi Tony,

Needs VBA I am afraid. Put this code in the worksheet code module
(right-click on the sheet name, select View Code, and paste this code in)

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
On Error GoTo ws_exit
With Target
If .Column = 5 Then
If IsNumeric(.Value) Then
If IsNumeric(.Offset(0, -2).Value) Then
.Offset(0, -2).Value = .Offset(0, -2).Value + .Value
End If
End If
ElseIf .Address = "$C$2" Then
If LCase(.Value) = "clear" Then
Range("C2:C" & Cells(Rows.Count, "C").End(xlUp).Row).Value =
vbNullString
Range("E2:E" & Cells(Rows.Count, "E").End(xlUp).Row).Value =
vbNullString
End If
End If
End With

ws_exit:
Application.EnableEvents = True
End Sub


To clear column C (and E), put the word 'clear' in cell C2, any case.

--

HTH

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