How to check multiple login or entry in excel

[Jonas230ph]

Hi Guys,

I'm a big fan of this site it's very helpful. though i came across with this concern or problem in excel wherein, I want to find out the latest log in time and the Last log out time of our employee the difficult part of it they have multiple log in and out on the system and should recognized 24 hour shift. I already tried the index and match formula in excel seems to be the result are not accurate or wrong can you help me guys regarding this concern. I'll attached the project or excel file im working on now, hope you can enlightenment me with this concern,

 

[mole999]

you could create a routine to get the environs value for the logged on user

Environ Command In Vba To Get Logged Windows User Name

and store that on a hidden sheet, and stamped with a date/time the entry

 

[NewOrderFac33]

Here's what I use to trap the Windows Login ID of the current user. Don't forget to include the declaration at the top of the code module where you add the function.

'For use with WinGetUserName
Public Declare Function GetUserName Lib "advapi32" Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long


'===========================================================
'GetUserName (include Declare statement (1) above)
'===========================================================


Function GetWinUserName() As String


    Dim szBuffer As String * 100
    Dim lBufferLen As Long


    lBufferLen = 100


    If CBool(GetUserName(szBuffer, lBufferLen)) Then
        GetWinUserName = Left$(szBuffer, lBufferLen - 1)
    Else
        GetWinUserName = CStr(Empty)
    End If


End Function


Sub ShowWinUserName()
    MsgBox (GetWinUserName)
End Sub

Cheers

Pete

 

[Jonas230ph]

Thanks guys

 

[Jonas230ph]

I have another concern in excel same issue though all data are coming from CMS AVAYA

[/URL][/IMG]

 

[mole999]

SO your call management system is already creating the statuses, you want to view that in excel (is that correct) I can only Guess Not Login has no activity for the user ID on the date, or have I missed something fundamental

 

[Jonas230ph]

SO your call management system is already creating the statuses, you want to view that in excel (Yes that correct) I can only Guess Not Login has no activity for the user ID on the date, or have I missed something fundamental--- on this issue i wanted to see the first log in time and the last log out time of our employees, since they have multiple log in CMS avaya, it's that easy solve by formula or VBA or combination of both?

 

[Jonas230ph]

By the way i already extracted the data from call management system seen on the lower part of the screenshot

 

[mole999]

I'm sure someone here can craft a lookup that returns the min and max values by day for the ID, beyond my skills though, how long is the data list ?

 

[Jonas230ph]

Approx 200 data entry, since its a full month of logs on the system.