Excel/ Access: StartTime, Duration

avn 0
Viewed 30

I have a set of data with the following headings:

Username; StartTime; Duration

I need to find some way to illustrate how many users are logged in at the same time (only if there is more than 1, list the usernames OR user count).

There seems to be information regarding if you want to check if a specific date falls within a start and end date, but I can't find something for my specific problem.

For example:

User StartTime Duration
UserA 02/13/17 13:15 00:29:00
UserB 02/13/17 13:20 00:30:00

I should get the information out that UserA and UserB were using the service at the same time.

ms-access excel
1 Answers

Calculating how many started within defined time period shouldn't be too difficult. I will use 5-minute increments as example. Calculate each user start time rounded up to nearest multiple of 5. Here is a helper function to do that:

Public Function RoundUp(dblVal As Double, dblTo As Double) As Double
Dim lngTestValue As Long
Dim dblTestValue As Double
Dim dblDenominator As Double
dblDenominator = -1 * dblTo
dblTestValue = dblVal / dblDenominator
lngTestValue = Int(dblTestValue)
RoundUp = -1 * lngTestValue * dblTo
End Function

Build query:

SELECT DateValue([StartTime]) AS Dte, 
       RoundUp(Hour([StartTime])*60+Minute([StartTime]),5) AS Minutes, 
       Count(Table1.User) AS CountOfUser
FROM Table1
GROUP BY DateValue([StartTime]), RoundUp(Hour([StartTime])*60+Minute([StartTime]),5);
Dte Minutes CountOfUser
2/13/2017 795 1
2/13/2017 800 1

If you want to see user details, don't do aggregate query. Instead build a report and use its Sorting & Grouping features with aggregate calculation in textbox.

June70
Related