max number in cell rest in other cell

  • Thread starter Thread starter Harberg
  • Start date Start date
H

Harberg

I want to enter a number in a cell (any value) and end up with a max. of 8 in
that cell. The remainder to be tranfered to another.
This to create a time-sheet where the normal and overtime hours are
automatically separated
 
Hi

I assume cells are formatted as time and hours are entered as 8:00.

This is an event code and has to be pasted into the code sheet for the
desired sheet. In this example hours are entered in B2:B10 and overtime are
transfered to C2:C10.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim TargetRange As Range

Set TargetRange = Range("B2:B10") ' Change to suit
Set isect = Intersect(Target, TargetRange)

If Not isect Is Nothing Then
If Target.Value > TimeValue("08:00:00") Then
mytime = Target.Value
Target.Value = TimeValue("08:00:00")
Target.Offset(0, 1) = mytime - TimeValue("08:00:00")
End If
End If
End Sub

Best regards,
Per
 
Back
Top