Number of users per half hour graph

We are getting sluggish responses to a certain program used by our organization between certain time frames. I would like to take a report created of when users logged in and out from a date range and graph it to show how many users were logged in either from a half hour stand point to every hour. I have a sample of the information I would like to use below. It is not the exact data of course, but it has the information needed.

UserID	Logon		Logout		Hours	Duration
User01	1/1/15 18:54	1/1/15 19:45	0.84	0:50:29
User02	1/2/15 6:09	1/2/15 8:15	2.11	2:06:27
User03	1/2/15 7:18	1/2/15 16:53	9.59	9:35:33
User03	1/2/15 7:58	1/2/15 9:43	1.75	1:45:06
User04	1/2/15 8:03	1/2/15 16:57	8.89	8:53:32
User01	1/2/15 8:08	1/2/15 9:29	1.35	1:21:01
User05	1/2/15 8:18	1/2/15 8:33	0.25	0:15:07
User05	1/2/15 8:37	1/2/15 10:07	1.5	1:30:04
User06	1/2/15 8:49	1/2/15 9:09	0.32	0:19:09
User07	1/2/15 9:06	1/2/15 9:58	0.88	0:52:35
User08	1/2/15 9:15	1/2/15 9:35	0.33	0:19:52
User09	1/2/15 9:38	1/2/15 9:38	0.01	0:00:19
User09	1/2/15 9:39	1/2/15 11:57	2.3	2:18:17
User03	1/2/15 9:55	1/2/15 12:01	2.1	2:05:52
User07	1/2/15 9:58	1/2/15 11:47	1.81	1:48:49
User05	1/2/15 10:10	1/2/15 12:02	1.85	1:51:14
User10	1/2/15 10:38	1/2/15 15:42	5.06	5:03:24
User11	1/2/15 10:46	1/2/15 12:03	1.29	1:17:16
User12	1/2/15 10:54	1/2/15 14:33	3.65	3:38:49
User02	1/2/15 11:00	1/2/15 11:00		0:00:10
User07	1/2/15 11:47	1/2/15 13:44	1.95	1:56:47
User05	1/2/15 13:00	1/2/15 13:20	0.34	0:20:15
User13	1/2/15 13:07	1/2/15 13:28	0.35	0:21:16
User05	1/2/15 13:29	1/2/15 13:36	0.12	0:07:16
User05	1/2/15 13:39	1/2/15 16:56	3.28	3:16:55
User07	1/2/15 13:44	1/2/15 13:52	0.12	0:07:23
User07	1/2/15 14:00	1/2/15 15:54	1.9	1:54:05
User01	1/2/15 14:00	1/2/15 16:25	2.4	2:24:12
User06	1/2/15 14:02	1/2/15 14:03	0.02	0:00:55
User06	1/2/15 14:03	1/2/15 14:04	0.03	0:01:39
User13	1/2/15 14:06	1/2/15 14:10	0.07	0:04:13
User14	1/2/15 14:07	1/2/15 14:35	0.48	0:28:37
User13	1/2/15 15:05	1/2/15 15:57	0.87	0:51:58
User07	1/2/15 16:05	1/2/15 16:08	0.06	0:03:24
User08	1/2/15 16:06	1/2/15 16:12	0.09	0:05:13
User07	1/2/15 16:09	1/2/15 18:44	2.6	2:35:43
User15	1/2/15 16:25	1/2/15 16:42	0.29	0:17:12
User13	1/2/15 16:34	1/2/15 16:36	0.03	0:01:59
User08	1/2/15 16:39	1/2/15 16:40	0.03	0:01:36
User13	1/2/15 16:56	1/2/15 16:56	0.01	0:00:30
User07	1/2/15 18:51	1/2/15 18:52	0.02	0:01:27
User07	1/3/15 18:58	1/3/15 22:14	3.28	3:16:38
User07	1/3/15 22:18	1/3/15 22:20	0.03	0:01:51
User07	1/3/15 22:20	1/3/15 22:23	0.06	0:03:20
User16	1/5/15 6:41	1/5/15 7:23	0.69	0:41:39
User02	1/5/15 6:55	1/5/15 10:40	3.75	3:45:04
User17	1/5/15 7:33	1/5/15 8:02	0.47	0:28:23
User03	1/5/15 7:46	1/5/15 10:49	3.05	3:02:59
User04	1/5/15 7:56	1/5/15 10:41	2.75	2:45:07
User18	1/5/15 8:03	1/5/15 10:48	2.75	2:45:06
User03	1/5/15 8:04	1/5/15 10:49	2.76	2:45:29
User05	1/5/15 8:21	1/5/15 9:21	1	1:00:04
User08	1/5/15 9:04	1/5/15 10:49	1.75	1:45:05
User19	1/5/15 9:10	1/5/15 10:40	1.5	1:30:06
User20	1/5/15 9:14	1/5/15 9:20	0.1	0:06:04
User10	1/5/15 9:17	1/5/15 9:37	0.33	0:19:32
User07	1/5/15 9:20	1/5/15 9:29	0.16	0:09:36
User20	1/5/15 9:20	1/5/15 9:28	0.12	0:07:25
User21	1/5/15 9:26	1/5/15 9:41	0.24	0:14:27
User20	1/5/15 9:28	1/5/15 9:36	0.14	0:08:33
User20	1/5/15 9:36	1/5/15 9:36		0:00:10
User07	1/5/15 9:36	1/5/15 9:39	0.05	0:02:54
User07	1/5/15 9:39	1/5/15 9:41	0.03	0:01:32
User07	1/5/15 9:41	1/5/15 10:07	0.44	0:26:07
User05	1/5/15 9:48	1/5/15 9:53	0.09	0:05:10
User05	1/5/15 9:59	1/5/15 10:44	0.75	0:45:08

Open in new window


I would like the graph to show How many users online per day per hour. Duration does not really matter, I believe. If not per hour, per half hour would work also. My problem seems to be taking usernames and giving them a qualifier so that excel will not count someone twice in the same hour.

Thank You,
Jason Dykstra
WalkerCountyTXAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saurabh Singh TeotiaCommented:
You need to do in this steps..Assuming you want to do the formula way...

Enclosed is the sample file for your reference where i added few extra columns to get the necessary result and then creating a summary of intervals out of the data which is basically what you are looking for...

Again it will count a user only once even in spite of multiple login / logouts..

Saurabh...
data-Graph.xlsx
0
WalkerCountyTXAuthor Commented:
That is great, thank you, it gets the gears rolling. Thank so much I will keep you informed.
0
Saurabh Singh TeotiaCommented:
Glad it helped..and sure let me know if you need any help.. :-)
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

Rob HensonFinance AnalystCommented:
Why would you be discounting when a single user logs on twice in a particular time period?

Surely that is still two login requests which are being slowed by system issues or whatever the problem is. The fact that it happened to be the same person logging back in at a later time is irrelevant. If the second login incidence was a different person you would still include it.

For example, lines 4 & 5 of your sample data are both User03:

He/she logged in at 9:43 for 01:45 hours, logging off at 11:28. He/she then logged in again at 16:53 and stayed logged in for 9:35.

Why should the second login be discounted, it could just as easily have been a different person.

Thanks
Rob H
0
Rob HensonFinance AnalystCommented:
I take that back, I was misreading the data, at least on that particular example.

User03 logged in at 7:18 for 9:35, logging off at 16:53
In addition there is record of the same user logging in 50 minutes later although they were still logged in according to the above entry.

Thanks
Rob H
0
WalkerCountyTXAuthor Commented:
I still take into account if they are loggins that are far enough apart or more. I do not take into account if the loggins are within the same time frame. For instance, I have a user that logged in at 9:38 and then again shows a log in at 9:39, it will only count that 1 time. I do not know why it is showing up like that in the logs, but for now I do not want to show that as 2 loggins.

On the other side of it, I have a user that logs in at 8:18 and then again at 8:37. In this case I do want to show that as 2 loggins.
0
Saurabh Singh TeotiaCommented:
That's how it's working in the example that i posted i have made it unique count in 30 Mins interval.. to see the same...
0
WalkerCountyTXAuthor Commented:
Ms. Teotia, I would first like to thank you very much because that is exactly the information I was asking for.

I thought I was good with Excel until I ran into this. I am now being asked if I can show how many users are actually Logged On during specific time frames instead of just showing logins for those time frames.

I see that the data shows me the logon and the logoff and the duration of their session. You do not see the information in the snippet shown, but I am seeing some durations of over 3 days (one was over 80 hours). What is the best option for showing how many users are logged in for each day during that same time frame.

So for instance:
Date		Time		Active
01/01/2015	07:30
                08:00
                08:30
                09:00
                09:30
                10:00
                10:30
                11:00
                11:30
                12:00
                12:30
                13:00
                13:30
                14:00
                14:30
                15:00
                15:30
                16:00
                16:30
                17:00
                17:30

Open in new window


And then it would go on to the next day. If this is too much to ask for please let me know.
0
Saurabh Singh TeotiaCommented:
I have a question for you will the data you have will be sorted in ascending order only?? In case of multiple logins?

Also can you give me sample data to look at in excel like i updated will develop a solution for you to work.

Saurabh...
0
WalkerCountyTXAuthor Commented:
I will work on this after lunch. And I just realized I put a Ms. in the reply above and did not mean to put any qualifiers. I was typing up an IM to someone and I think i got things crossed. Sorry if you are not a Ms. I did not mean to offend.
0
Saurabh Singh TeotiaCommented:
It's okay buddy i ignored that..at times i do goof up myself when writing to someone in us just looking in their names can be confusing..No offense taken.. :-)
0
WalkerCountyTXAuthor Commented:
Here is an excel sheet that ranges from 01/01/2015 until 01/09/2015. I have broken down the information a little more in more columns, and it has the first solution integrated into it. Columns C&D and H&I break out the Logon and Logoff Date and time into Date in one column and time into another. Columns E&F come from your first solution and round the date and time into 30 minute intervals and then strip off the date and leave just the time.

I also added a new sheet that has some data entry added just in case it helps. I believe that too much information is better than not enough. If you need me to do any more on my side please let me know.
User-Report-Modified-.xlsx
0
Saurabh Singh TeotiaCommented:
Walker,

I wrote a UDF to do this and to make it simple...

Here is the code for your reference...

Function getcount(r As Range, r1 As Range, r2 As Range, z As Long, y As Long) As Long

    Dim c As Range
    Dim cl As Collection

    Set cl = New Collection

    On Error Resume Next
    For Each c In r2
        If c.Offset(0, z).Value <= (r1.Value + r.Value) And c.Offset(0, y).Value >= (r1.Value + r.Value) Then
            cl.Add CStr(c.Value), CStr(c.Value)
        End If
    Next c

    getcount = cl.Count

End Function

Open in new window


What it does it looks for unique logins during the time and give you that answer...

The input in this is
1. Input-->Date
2. Input-->Interval
3. Input-->First Column where your users are..
4. Input-->Your login time of the dataset like in the example its 1 column away from first column
5. Input-->Your logoff time in the dataset like in the example its 6 column away from first column...

Enclosed is your workbook for your reference...

Saurabh...
User-Report-Modified-.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
WalkerCountyTXAuthor Commented:
Thank you very much, I am going to integrate this into my main sheet and do some data entering. I will keep you informed.
0
WalkerCountyTXAuthor Commented:
This was great, it gave me everything I needed for data. Thank you very much!
I made my second sheet show 1 week of data from 7 AM to 6 PM. Then changed it so you can change the first date and the rest change automatically. Added a row underneath the date that tells you what day of the week that day is as well.
Once again, thank you! This is above and beyond.
0
Saurabh Singh TeotiaCommented:
Glad it worked.. :-)

Saurabh...
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.