Calculate Timings For a Day

[krishgopalakrishnan]

I have five columns. Column A with Serial Number, Column B with Date, Column C with Time, Column D with Entry Details, Column E with Name. Need to calculate difference of IN and Out. Having 40 thousand data like this in a sheet with different date and timings ? How can we calculate ?




S.No | Date | Time | Entry |Name
1 4-Aug-14 0:36 OUT anil
2 4-Aug-14 0:47 IN anil
3 4-Aug-14 1:59 OUT vinoth
4 4-Aug-14 2:01 IN vinoth
5 4-Aug-14 2:26 OUT sharma
6 4-Aug-14 2:28 OUT Ram
7 4-Aug-14 2:31 IN Ram
8 4-Aug-14 2:32 OUT jai
9 4-Aug-14 2:41 IN sharma
10 5-Aug-14 0:36 OUT anil
11 5-Aug-14 0:47 IN anil
12 5-Aug-14 1:59 OUT vinoth
13 5-Aug-14 2:01 IN vinoth
14 5-Aug-14 2:26 OUT sharma
15 5-Aug-14 2:28 OUT Ram
16 5-Aug-14 2:31 IN Ram
17 5-Aug-14 2:32 OUT jai
18 5-Aug-14 2:41 IN sharma

 

[venkat1926]

try this macro

Sub test()
Dim r As Range, dday As Long, ttime As Double
Application.ScreenUpdating = False
Range("F1").EntireColumn.Cells.Clear
Set r = Range("F3")
Do
If Cells(r.Row, "B") = Cells(r.Row - 1, "B") + 1 Then
dday = 1
Else
dday = 0
End If
ttime = Cells(r.Row, "C") + dday - Cells(r.Row - 1, "C")
r = ttime
r.NumberFormat = "h:mm;@"
Set r = r.Offset(2, 0)
If Len(r.Offset(0, -1)) = 0 Then Exit Do
Loop
Application.ScreenUpdating = True
MsgBox "macro over. see column F"
End Sub

 

[mfexcel]

a PivotTable would help relayout your data for easy calculation...

[TABLE="width: 324"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Row Labels[/TD]
[TD]IN[/TD]
[TD]OUT[/TD]
[/TR]
[TR]
[TD]anil[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]04-Aug-14[/TD]
[TD="align: right"]0:47:00[/TD]
[TD="align: right"]0:36:00[/TD]
[/TR]
[TR]
[TD]05-Aug-14[/TD]
[TD="align: right"]0:47:00[/TD]
[TD="align: right"]0:36:00[/TD]
[/TR]
[TR]
[TD]jai[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]04-Aug-14[/TD]
[TD][/TD]
[TD="align: right"]2:32:00[/TD]
[/TR]
[TR]
[TD]05-Aug-14[/TD]
[TD][/TD]
[TD="align: right"]2:32:00[/TD]
[/TR]
[TR]
[TD]Ram[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]04-Aug-14[/TD]
[TD="align: right"]2:31:00[/TD]
[TD="align: right"]2:28:00[/TD]
[/TR]
[TR]
[TD]05-Aug-14[/TD]
[TD="align: right"]2:31:00[/TD]
[TD="align: right"]2:28:00[/TD]
[/TR]
[TR]
[TD]sharma[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]04-Aug-14[/TD]
[TD="align: right"]2:41:00[/TD]
[TD="align: right"]2:26:00[/TD]
[/TR]
[TR]
[TD]05-Aug-14[/TD]
[TD="align: right"]2:41:00[/TD]
[TD="align: right"]2:26:00[/TD]
[/TR]
[TR]
[TD]vinoth[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]04-Aug-14[/TD]
[TD="align: right"]2:01:00[/TD]
[TD="align: right"]1:59:00[/TD]
[/TR]
[TR]
[TD]05-Aug-14[/TD]
[TD="align: right"]2:01:00[/TD]
[TD="align: right"]1:59:00[/TD]
[/TR]
</tbody>[/TABLE]


However, why all the OUT time are smaller than IN time in your example?