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...

Row Labels	IN	OUT
anil
04-Aug-14	0:47:00	0:36:00
05-Aug-14	0:47:00	0:36:00
jai
04-Aug-14		2:32:00
05-Aug-14		2:32:00
Ram
04-Aug-14	2:31:00	2:28:00
05-Aug-14	2:31:00	2:28:00
sharma
04-Aug-14	2:41:00	2:26:00
05-Aug-14	2:41:00	2:26:00
vinoth
04-Aug-14	2:01:00	1:59:00
05-Aug-14	2:01:00	1:59:00

<colgroup><col><col><col></colgroup><tbody>
</tbody>

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