So it's not actually the same layout as the example workbook?
Is like in post 18
Say in A2:A54 are data like this:
06:30 cell A2
21:45 cell A3
22:30 cell A4
13:35 cell A54
and result will be in cells (B2:B54), like 08:15, 12:10, 07:45....
I need also to calculate working hour like this: start time 22:30 and end time - say 05:45
Attach a workbook.
I keep asking why your times are like this. If it's timekeeping software then I would look for a better option.
It's a rubbish program. it really should be generating total hours worked,
Try this run the macro and the hours are entered into Column C.
Thank you RoyUK.
VBA code working perfect.
You can remove the two lines starting with Debug.Print, they were only used for testing. In fact this runs just as well.Code
Sub ProduceTimes() Dim rCl As Range Dim StartTime As Date, EndTime As Date, Mins As Long, lHours As Long With Sheet1 For Each rCl In .Range(.Cells(2, 1), .Cells(.Rows.Count, 1).End(xlUp)) StartTime = TimeValue(Left(Trim(rCl.Value), 5)) EndTime = TimeValue(Right(Trim(rCl.Value), 6)) If StartTime < EndTime Then rCl.Offset(, 1).Value = EndTime - StartTime Else: rCl.Offset(, 1).Value = (24 - StartTime) + EndTime End If rCl.Offset(, 1).NumberFormat = "h:m" Next rCl End With End Sub
Yes, I already deleted them.