Thread: Login - Logout report

Attached is the login-logout report for my team . Which gives me employee login time & logoff time . The dump has few entries which are repeated . For ex : In Cell D5 employee logged in , E5 has a logout time ( which is incorrect ) , while cell D6 has a login time ( which is incorrect ) , E6 has a logout time which is correct . So for 4th Dec I wanted to have D5 ( for login ) - E6 ( for logout ) . In the same way , I have many cells which has been repeated .


Hint - (1) Days shouldn't not be repeated . (2) Logout time shuld be greater than the login time for that day ( & rest should be eliminated for that day )


I'm not sure if I have explained it clearly or not. Do let me know if I can provide any other information .

Why not just push the data into a Pivot ?

Row Fields:
-- Name
-- Date

Data Fields:
-- Login Time set to MIN
-- Logout Time set to MAX
set number format of the above to h:mm:ss

This will give you per employee one record per day with their respective IN/OUT times
(above assumes shifts don't cross midnight)

This should take care of it for you:

Code:

Option Explicit

Sub LogInOutMerge()
'JBeaucaire  (12/11/2009)
Dim LR As Long, i As Long
Application.ScreenUpdating = False

LR = Range("A" & Rows.Count).End(xlUp).Row

Columns("A:E").Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range("B2"), _
    Order2:=xlAscending, Key3:=Range("D2"), Order3:=xlAscending, Header:=xlYes, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
    DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:=xlSortNormal

For i = LR To 3 Step -1
    If Cells(i, "B") = Cells(i - 1, "B") And Cells(i, "C") = Cells(i - 1, "C") Then
        If Cells(i, "E") > Cells(i - 1, "E") Then
            Cells(i - 1, "E") = Cells(i, "E")
            Rows(i).Delete xlShiftUp
        End If
    End If
Next i

Application.ScreenUpdating = True
End Sub

===========
How/Where to install the macro:

1. Open up your workbook
2. Get into VB Editor (Press Alt+F11)
3. Insert a new module (Insert > Module)
4. Copy and Paste in your code (given above)
5. Get out of VBA (Press Alt+Q)
6. Save your sheet

The macro is installed and ready to use. Press Alt-F8 and select LogInOutMerge from the macro list.

Try this on a copy of your data.

If that takes care of your need, be sure to EDIT your original post, click Go Advanced and mark the PREFIX box [SOLVED].


(Also, use the blue "scales" icon in our posts to leave Reputation Feedback, it is appreciated)

This will work on the shown layout, and it will adjust the columns to display the times for you:

Code:

Option Explicit

Sub LogInOutMerge()
'JBeaucaire  (12/11/2009)
Dim LR As Long, i As Long
Application.ScreenUpdating = False

LR = Range("A" & Rows.Count).End(xlUp).Row

Columns("C:C").TextToColumns Destination:=Range("C1"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
    Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
    :=Array(1, 3), TrailingMinusNumbers:=True
Columns("D:D").TextToColumns Destination:=Range("D1"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
    Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
    :=Array(1, 3), TrailingMinusNumbers:=True
    
Columns("C:D").NumberFormat = "[$-409]h:mm:ss AM/PM;@"
Columns("C:D").AutoFit

Columns("A:E").Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range("B2"), _
    Order2:=xlAscending, Key3:=Range("C2"), Order3:=xlAscending, Header:=xlYes, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
    DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:=xlSortNormal

    For i = LR To 3 Step -1
        If Cells(i, "B") = Cells(i - 1, "B") And Cells(i, "E") = Cells(i - 1, "E") Then
            If Cells(i, "D") > Cells(i - 1, "D") Then
                Cells(i - 1, "D") = Cells(i, "D")
                Rows(i).Delete xlShiftUp
            End If
        End If
    Next i

Application.ScreenUpdating = True
End Sub