15,900,816 members
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```

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

## Solution 1

See LOOKUP function - Office Support[^]. You can run commands in Excel while recording a macro so most of the work is done for you.

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

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.

## Solution 2

[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, _

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

v2
[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

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