Hello All,
I'm trying to map who is present based off of login data.
I'd like to be able to plug in a range & Have Excel spit out a number of how many people were logged in during a certain time period.
(Say each hour, or 0:00 in the morning to 6:00 in the morning etc.)
A dataset sample looks like this, it is in military time for ease of calculation:
<tbody>
</tbody>
<tbody>
</tbody>
<tbody>
</tbody>
... which repeats for many users and many dates etc..
/////////////////////////////////////////////////////////////////////////////////////////////
I have created some great Gannt Charts by having excel calculate duration between the times-
<tbody>
</tbody>Etc.
These charts clearly show how many folks are logged in to the naked eye, but I can't get excel to spit out the actual number of people during a given timeframe:
Ex:
<tbody>
</tbody>
////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
I've tried Countif formulas, from similar threads here on employee charting, and a variety of other codes to no avail.
It appears the Countif formula simply takes the dataset I have and counts multiple log.
For Example:
=COUNTIFS([Data],">="&[Time Period to Check for Start],[Data],"<"&[Time Period to Check For End)
=COUNTIFS($C$39:$J$79,">="&N39,$C$39:$J$79,"<"&O39)
But these always provide faulty countifs due to the breaks and multiple login/logouts per person.
Any Ideas???
I'm trying to map who is present based off of login data.
I'd like to be able to plug in a range & Have Excel spit out a number of how many people were logged in during a certain time period.
(Say each hour, or 0:00 in the morning to 6:00 in the morning etc.)
A dataset sample looks like this, it is in military time for ease of calculation:
| User Name | 5/1/2017 | 5/2/2017 | 5/3/2017 | 5/4/2017 | 5/5/2017 |
| Login A | 0:00 | 13:00 | 2:00 | 9:00 | 13:02 |
| Logout A | 6:00 | 14:00 | 3:00 | 13:00 | 15:55 |
| Login B | 12:30 | 12:33 | 13:54 | ||
| Logout B | 16:22 | 13:07 | 15:00 | ||
| Login C | 17:03 | ||||
| Logout C | 18:09 | ||||
| Login D | 18:11 | ||||
| Logout D | 23:59 |
<tbody>
</tbody>
| User Name 2 |
<tbody>
</tbody>
| Login A | Time | Time | Time | Time | Time etc. |
<tbody>
</tbody>
... which repeats for many users and many dates etc..
/////////////////////////////////////////////////////////////////////////////////////////////
I have created some great Gannt Charts by having excel calculate duration between the times-
| User ID | In A | Out A | Duration A | In B | A-B Duration Change | Out B | Duration B | In C | B-C Duration Change | Out C | Duration C | In D | C-D Duration Change | Out D | Duration D |
| User 1 | 7:00 | 12:30 | 5:30 | 13:00 | 0:30 | 14:00 | 1:00 | 0:00 | 10:00 | 0:00 | 0:00 | 0:00 | 0:00 | 0:00 | 0:00 |
<tbody>
</tbody>
These charts clearly show how many folks are logged in to the naked eye, but I can't get excel to spit out the actual number of people during a given timeframe:
Ex:
| User 1 | ----- | ------ | ------ | ||||
| User 2 | |||||||
| User 3 | ------ | ------ | |||||
| User 4 | |||||||
| User 5 | ------- | ||||||
| 0:00 | 1:00 | 2:00 | 3:00 | 4:00 | 5:00 | 6:00 |
<tbody>
</tbody>
////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
I've tried Countif formulas, from similar threads here on employee charting, and a variety of other codes to no avail.
It appears the Countif formula simply takes the dataset I have and counts multiple log.
For Example:
=COUNTIFS([Data],">="&[Time Period to Check for Start],[Data],"<"&[Time Period to Check For End)
=COUNTIFS($C$39:$J$79,">="&N39,$C$39:$J$79,"<"&O39)
But these always provide faulty countifs due to the breaks and multiple login/logouts per person.
Any Ideas???
