Hi, I’ve got two columns. Time of log-in and time of log-out for users. I want to count how many users there are actively logged-in at particular time. I can’t find out how to calculate for values “between this and that”
For example:
8:12 10:54 John
I can do IF to match 8 and 10 and it works but for 9 there is no match although there should be - user is logged-in between those values.
Thanks for any help.
If the hours are enter as time (not as text) in the columns:
=SUMPRODUCT(A1:A100>=TIME(9;0;0);B1:B100<TIME(10;0;0))
if the time values are enter as text:
=SUMPRODUCT(TIMEVALUE(A1:A100)>=TIME(9;0;0);TIMEVALUE(B1:B100)<TIME(10;0;0))I would use multiple columns to get started as in the following example.
Enter the time you want to view the results for in cell F2. The formulas in column D compare the login time with the time in F2. Column E compares the logout time to the time in F2. And column F shows if they are logged in as of the time you entered. So in the first example, 4 users are logged in as of 8:00am (see cell D11 or F11). In example 2, there is one person logged in as of 4:01pm (see E23 or F23). The example spreadsheet is attached. You can further refine the example, for instance by hiding columns D & E or combining the formulas in D-F to get a formula that would be suitable for one cell.
