Two columns - treat them as a range of values

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:


if the time values are enter as text:


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.