Click here to Skip to main content
15,900,816 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
Hi My Excel sheet looks something like this(It has three column as "Date and Time", "event Description" and "Name")

Need to calculate the average time an employee has spent inside that room by excel macros vba code.


Date and Time	 Event Description	Name
8/1/2017 11:43:02	Entry granted	shibashish
8/1/2017 11:58:48	Exit granted	shibashish
8/1/2017 12:04:28	Entry granted	shibashish
8/1/2017 12:57:20	Exit granted	shibashish
8/1/2017 13:54:49	Entry granted	shibashish
8/1/2017 14:09:06	Exit granted	shibashish
8/1/2017 14:19:26	Entry granted	shibashish
8/1/2017 15:34:24	Exit granted	shibashish
8/1/2017 16:20:11	Entry granted	shibashish
8/1/2017 17:25:23	Exit granted	shibashish
8/1/2017 18:36:16	Entry granted	shibashish
8/1/2017 19:21:22	Exit granted	shibashish



Please help me. Thanks in advance.

What I have tried:

Actually i want to show the average time of an employee inside the room by checking the in and out time.
Posted
Updated 11-Oct-17 21:42pm

See LOOKUP function - Office Support[^]. You can run commands in Excel while recording a macro so most of the work is done for you.
 
Share this answer
 
Comments
[no name] 9-Oct-17 4:49am    
Hi Richard, Thanks for your reply. But for my project i need average time where "Name"="Some value". So i need this in Macro code.

Please help me if possible.
Richard MacCutchan 9-Oct-17 5:08am    
Sorry, I do not have the time to write your code.
Maciej Los 9-Oct-17 5:49am    
Richard, Lookup function is used to find single value from referenced range. OP need to count the time spend in a room, then to calculate average.
Richard MacCutchan 9-Oct-17 5:51am    
Yes. I understand that. I was just offering a starting point to find some appropriate functions.
[EDIT]
To be able to calculate average time, you have to sort data by the date and user and then to "merge" them. What i mean? You have to create another set of data, in which entry time and exit time will be close each other (in one row). A destination sheet should looks like:
A - Name
B - Date
C - Entry time
D - Exit time
E - Time (minutes)


According to below data:
EventNo	dtm	Name	Status
1	2017-08-11 10:46	shibashish	Entry
2	2017-08-11 17:50	shibashish	Exit
2	2017-08-11 18:50	shibashish	Entry
5	2017-01-12 19:00	ranjan	Entry
6	2017-01-12 19:21	ranjan	Exit
7	2017-08-11 20:05	ranjan	Entry
7	2017-08-11 20:05	shibashish	Exit
8	2017-08-11 21:55	ranjan	Exit
9	2017-08-12 12:46	shibashish	Entry
10	2017-08-12 14:35	shibashish	Exit
11	2017-08-12 16:20	shibashish	Entry
12	2017-08-12 18:07	shibashish	Exit


A macro should looks like:
VB
Option Explicit

Sub MergeEvents()
    Dim srcWsh As Worksheet, dstWsh As Worksheet, pvtWsh As Worksheet
    Dim i As Long, j As Long
    
    On Error GoTo Err_MergeEvents
    
    'define "source" sheet
    Set srcWsh = ThisWorkbook.Worksheets(1)
    'sort data by user name (col. C) and date (col. B)
    'get last row
    i = srcWsh.Range("D" & srcWsh.Rows.Count).End(xlUp).Row
    srcWsh.Sort.SortFields.Clear
    srcWsh.Range("A1:D" & i).Sort Key1:=srcWsh.Range("C1"), Order1:=xlAscending, _
            Key2:=srcWsh.Range("B1"), Order2:=xlAscending, Header:=xlYes
    
    'define "destination" sheet
    Set dstWsh = ThisWorkbook.Worksheets(2)
    With dstWsh
        .UsedRange.Clear
        .Range("A1") = "Name"
        .Range("B1") = "Date"
        .Range("C1") = "Entry time"
        .Range("D1") = "Exit time"
        .Range("E1") = "Time (minutes)"
        .Range("A1:E1").Font.Bold = True
    End With
    
    'first row is a header, so start from row no. 2
    i = 2
    j = 2
    Do While srcWsh.Range("A" & i) <> ""
        If srcWsh.Range("D" & i) Like "Exit*" Then GoTo SkipNext
        'copy name
        dstWsh.Range("A" & j) = srcWsh.Range("C" & i)
        'date
        dstWsh.Range("B" & j) = CDate(Format(srcWsh.Range("B" & i), "yyyy-MM-dd"))
        'entry time
        dstWsh.Range("C" & j) = Format(srcWsh.Range("B" & i), "HH:nn")
        'exit time
        dstWsh.Range("D" & j) = Format(srcWsh.Range("B" & i + 1), "HH:nn")
        'get time difference in minutes
        dstWsh.Range("E" & j) = DateDiff("n", CDate(srcWsh.Range("B" & i)), CDate(srcWsh.Range("B" & i + 1)))
        j = j + 1
SkipNext:
        i = i + 1
    Loop
   
    srcWsh.UsedRange.Columns.AutoFit

    'define location for pivot table
    Set pvtWsh = ThisWorkbook.Worksheets(3)
    pvtWsh.Cells.Clear
    AddMyPivot dstWsh, dstWsh.Name & "!" & dstWsh.Range("A1:E" & j - 1).Address, pvtWsh.Range("A3")
    pvtWsh.Activate

Exit_MergeEvents:
    On Error Resume Next
    Set srcWsh = Nothing
    Set dstWsh = Nothing
    Set pvtWsh = Nothing
    Exit Sub
    
Err_MergeEvents:
    MsgBox Err.Description, vbExclamation, "Error no. " & Err.Number
    Resume Exit_MergeEvents

End Sub

Sub AddMyPivot(ByRef dstWsh As Worksheet, ByVal src As String, ByVal dstLocation As Range)
    Dim i As Integer, pc As PivotCache, pt As PivotTable
        
    Set pc = dstWsh.Parent.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=src, Version:=xlPivotTableVersion14)
    Set pt = pc.CreatePivotTable(TableDestination:=dstLocation, TableName:="mypt1")
    
    With pt.PivotFields("Name")
        .Orientation = xlRowField
        .Position = 1
    End With
    With pt.PivotFields("Date")
        .Orientation = xlColumnField
        .Position = 1
    End With
    
    pt.AddDataField pt.PivotFields("Time (minutes)"), "Average time (minutes)", xlAverage
    dstWsh.Parent.ShowPivotTableFieldList = False
End Sub


Results:
Sheet2 (data which will be used by pivot table)
Name	Date	Entry time	Exit time	Time (minutes)
ranjan	2017-01-12	19:00	19:21	21
ranjan	2017-08-11	20:05	21:55	110
shibashish	2017-08-11	10:46	17:50	424
shibashish	2017-08-11	18:50	20:05	75
shibashish	2017-08-12	12:46	14:35	109
shibashish	2017-08-12	16:20	18:07	107

Sheet3
Average time (minutes)				
			2017-01-12	2017-08-11	2017-08-12	Total
ranjan		21			110					65,5
shibashish				249,5			108		178,75
Total		21			203			108		141


Final note: This is a bonus for you. Next time - do not expect that some one will do the job for you.
 
Share this answer
 
v2
Comments
[no name] 12-Oct-17 2:02am    
Thanks Maciej Los.
This code will not working for below scenario.

Suppose two people entered inside that room in different date.

Result should be come like First person average time on first date,Same first person average time on other day. same will be apply for other persons.
Sample data would looks like as below
EventNo dtm Name Status
1 8/11/2017 10:46 shibashish Entry
2 8/11/2017 17:50 shibashish Exit
3 8/12/2017 17:51 shibashish Entry
4 8/12/2017 18:11 shibashish Exit
5 12/1/2017 19:00 ranjan Entry
6 12/1/2017 19:21 ranjan Exit
7 8/11/2017 20:05 ranjan Entry
8 8/11/2017 21:55 ranjan Exit

And expected result should be like below
Name Date AverageTime
shibashish 8/11/2017 7:04
shibashish 8/12/2017 0:20
ranjan 12/1/2017 0:21
ranjan 8/11/2017 1:50


Please help me for this scenario.
Thanks in advance.
Maciej Los 12-Oct-17 2:12am    
Well, original question did not contain such as requirements. Nevertheless...
You have 2 options:
1) improve my code, by adding 2 conditions for checking if:
- date is changing,
- user name is changing
In both cases, you have to add one row to add average time for each date and user

2) create pivot table, which already can group data by date and user.

Note: You should accept all helful solutions (use green button).

Cheers,
Maciej
[no name] 12-Oct-17 3:02am    
Thanks a lot for your quick response.
Could you please add the condition there. i am trying the same but getting lots of issues to get the output.

Regards
Shibashish
[no name] 12-Oct-17 3:37am    
If in excel sheet my data is in random order like below. The above solution gives me wrong output.

EventNo dtm Name Status
1 8/11/2017 10:46 shibashish Entry
5 12/1/2017 19:00 ranjan Entry
2 8/11/2017 17:50 shibashish Exit
6 12/1/2017 19:21 ranjan Exit
7 8/11/2017 20:05 ranjan Entry
2 8/11/2017 18:50 shibashish Entry
7 8/11/2017 20:05 shibashish Exit
8 8/11/2017 21:55 ranjan Exit
Maciej Los 12-Oct-17 5:18am    
Check updated answer.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900