Just reformat the cell

from
Offset(0, 5).Value = TotalHours

to
Offset(0, 5).Value = TotalHours
Offset(0, 5).numberformat = "HH:MM"


"MJKelly" wrote:


Hi,

The bit which calculates the time between start and finish is:-


StartTime = r1.Value
EndTime = r1.Offset(0, 1)
TotalHours = EndTime - StartTime


After creating the new workbook using a template target sheet whcih is
copied numerous times, the code is (after this code I just format the
time cells as in my previous post): -

Dim r1 As Range
Dim Sh As Worksheet
Dim sh1 As Worksheet
Dim Person As String
Dim PayNo As String
Dim StartTime As Date
Dim EndTime As Date
Dim TotalTime As Date
Dim Task As String
Dim TargetShift As String
Dim Target552 As String
Dim Drop As Range

ThisWorkbook.Activate

For Each r1 In ThisWorkbook.Sheets("Overtime").Range("D3:D20")
If Not r1.Value = "" Then


If r1.Value = 0.25 Then TargetShift = "Early"
If r1.Value = 0.58264 Then TargetShift = "Late"
If r1.Value = 0.91597 Then TargetShift = "Night"
If r1.Value < 0.25 Then TargetShift = "Night"

Select Case r1.Offset(0, 4).Value
Case Is = "Proc M"
Task = "Processing"
Case Is = "XD"
Task = "Cross Docking"
End Select

Person = r1.Offset(0, -2)
PayNo = r1.Offset(0, -3)
StartTime = r1.Value
EndTime = r1.Offset(0, 1)
TotalHours = EndTime - StartTime

If TargetShift = "Early" And Task = "Processing" Then
Target552 = "Early Processing"
If TargetShift = "Late" And Task = "Processing" Then
Target552 = "Late Processing"
If TargetShift = "Night" And Task = "Processing" Then
Target552 = "Night Processing"
If TargetShift = "Early" And Task = "Cross Docking"
Then Target552 = "Early Cross Docking"
If TargetShift = "Late" And Task = "Cross Docking"
Then Target552 = "Late Cross Docking"
If TargetShift = "Night" And Task = "Cross Docking"
Then Target552 = "Night Cross Docking"

For Each sh1 In Workbooks(NewBook).Sheets
sh1.Activate
If sh1.Name = Target552 Then

ActiveSheet.Range("A10000").End(xlUp).Offset(1,
0).Select
With Selection

.Offset(0, 0).Value = Person
.Offset(0, 1).Value = PayNo
.Offset(0, 3).Value = StartTime
.Offset(0, 4).Value = EndTime
.Offset(0, 5).Value = TotalHours

End With

End If
Next sh1
End If

Next r1