Excel help please

[Andy.]

I have a list of log in and log out times per user, and need to know the maximum number of users logged in per day.

The data looks like this... I want to know that on the 29/06/2013, there was a maximum of x users as user 1001 had logged out before user 1004 had arrived etc.

Any ideas on how I can do this?

Rowguid	Loginid	Action	DateTimeStamp
1	1001	Logon	30/06/2013 23:50
2	1002	Logon	30/06/2013 23:50
3	1003	Logoff	30/06/2013 23:47
4	1004	Logoff	30/06/2013 23:31
5	1002	Logon	30/06/2013 22:57
6	1001	Logon	30/06/2013 22:55
7	1005	Logon	30/06/2013 22:46
8	1005	Logon	30/06/2013 22:46
9	1006	Logoff	30/06/2013 22:02
10	1880	Logon	30/06/2013 21:53
11	1880	Logon	30/06/2013 21:53
12	1003	Logon	30/06/2013 20:03
13	1003	Logon	30/06/2013 15:57
14	1004	Logon	30/06/2013 14:37
15	1006	Logon	30/06/2013 13:26
16	1880	Logoff	30/06/2013 06:58
17	1880	Logoff	30/06/2013 06:58
18	1001	Logon	29/06/2013 23:50
19	1002	Logon	29/06/2013 23:49
20	1002	Logoff	29/06/2013 23:47
21	1880	Logon	29/06/2013 23:02
22	1880	Logon	29/06/2013 23:02
23	1001	Logon	29/06/2013 22:56
24	1002	Logon	29/06/2013 22:44
25	1004	Logoff	29/06/2013 18:12
26	1006	Logon	29/06/2013 13:34
27	1006	Logon	29/06/2013 13:33
28	1006	Logoff	29/06/2013 13:32
29	1006	Logon	29/06/2013 13:31

 

[SC03OTT]

Ok, so I did this in Excel 2010.

First off, change the DateTimeStamp coumn to just show dates (right click - format - date). Highlight all the data, then click the 'Data' tab. Click 'Subtotal' and choose 'At Each Change in - DateTimeStap', 'Use Function - Count' and click ok. This will show you how many logons in total there were per day. It does not show how many unique logons there were however. Is that what you actually wanted?

 

[Deeg]

Can you concat a couple of the data points to give you a unique identifier, then do a countif on the unique identifier?

 

[Chrisgti6]

I'd start with some pivot tables personally. If still struggling tomorrow I'll knock something up at work.

 

[The Boosh!]

Definitely a pivot table.

 

[Andy.]

I can make a pivot to display log ins per day, but I need a count of the maximum simultaneous users, which is where I get lost.


Sent from my iPhone using Tapatalk 2

 

[Deeg]

If you concat the date and user id, that should give you a unique reference which you can then work with on each date.

 

[Andy.]

Cheers Deeg. I delegated it to our reporting department in the end so that they could work their witchcraft on it.

Excel for dummies didn't get me through this one.

 

[The Boosh!]

You sorted now Andy?

 

[Andy.]

Yep thanks! I just let the experts do their thing rather than waste any more of my time trying to do it myself.