excelforeverything
New Member
- Joined
- Dec 10, 2018
- Messages
- 2
Hello all,
Hopefully I can explain this clearly... I am currently trying to calculate the hours/minutes from Time Range A (which is a recurring schedule of APPROVED time ranges) that do NOT overlap with Time Range B (which is a larger data set of different login periods). Basically, users will have allotted time ranges through out the day that they are allowed to login at, if the time range that they are logged in for is longer or anywhere out side of their allotted time range then I need to be able calculate those hour/minutes. My process right now has many helper columns and some of my equations won't catch accurately calculate what I'm trying to do in all situations.
For example, Time Range A, AKA an approved recurring schedule for the month of November:
User Name Start Time End Time
User 1 0:00 6:45
User 1 9:32 11:45
User 1 19:38 21:35
User 1 20:54 23:59
User 2 6:25 10:00
Time Range B, AKA all the time ranges that users had logged in:
[TABLE="width: 441"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]User Name[/TD]
[TD]Start Time[/TD]
[TD]Start Date[/TD]
[TD]End Time[/TD]
[TD]End Date[/TD]
[/TR]
[TR]
[TD]User 1[/TD]
[TD="align: right"]19:55:14[/TD]
[TD="align: right"]11/02/18[/TD]
[TD="align: right"]20:57:02[/TD]
[TD="align: right"]11/02/18[/TD]
[/TR]
[TR]
[TD]User 1[/TD]
[TD="align: right"]8:41:40[/TD]
[TD="align: right"]11/03/18[/TD]
[TD="align: right"]11:56:10[/TD]
[TD="align: right"]11/03/18[/TD]
[/TR]
[TR]
[TD]User 1[/TD]
[TD="align: right"]11:44:16[/TD]
[TD="align: right"]11/04/18[/TD]
[TD="align: right"]11:52:24[/TD]
[TD="align: right"]11/04/18[/TD]
[/TR]
[TR]
[TD]User 1[/TD]
[TD="align: right"]11:57:18[/TD]
[TD="align: right"]11/04/18[/TD]
[TD="align: right"]12:50:40[/TD]
[TD="align: right"]11/04/18[/TD]
[/TR]
[TR]
[TD]User 1[/TD]
[TD="align: right"]11:07:00[/TD]
[TD="align: right"]11/05/18[/TD]
[TD="align: right"]12:06:02[/TD]
[TD="align: right"]11/05/18[/TD]
[/TR]
[TR]
[TD]User 1[/TD]
[TD="align: right"]8:58:11[/TD]
[TD="align: right"]11/06/18[/TD]
[TD="align: right"]13:09:56[/TD]
[TD="align: right"]11/06/18[/TD]
[/TR]
[TR]
[TD]User 1[/TD]
[TD="align: right"]11:41:48[/TD]
[TD="align: right"]11/06/18[/TD]
[TD="align: right"]13:05:11[/TD]
[TD="align: right"]11/06/18[/TD]
[/TR]
[TR]
[TD]User 1[/TD]
[TD="align: right"]20:12:30[/TD]
[TD="align: right"]11/06/18[/TD]
[TD="align: right"]21:29:57[/TD]
[TD="align: right"]11/06/18[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 441"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]User 1[/TD]
[TD="align: right"]8:54:08[/TD]
[TD="align: right"]11/02/18[/TD]
[TD="align: right"]11:18:19[/TD]
[TD="align: right"]11/02/18[/TD]
[/TR]
[TR]
[TD]User 2[/TD]
[TD="align: right"]9:22:02[/TD]
[TD="align: right"]11/02/18[/TD]
[TD="align: right"]9:31:46[/TD]
[TD="align: right"]11/02/18[/TD]
[/TR]
[TR]
[TD]User 2[/TD]
[TD="align: right"]9:31:55[/TD]
[TD="align: right"]11/02/18[/TD]
[TD="align: right"]10:00:12[/TD]
[TD="align: right"]11/02/18[/TD]
[/TR]
[TR]
[TD]User 2[/TD]
[TD="align: right"]15:46:16[/TD]
[TD="align: right"]11/06/18[/TD]
[TD="align: right"]16:09:16[/TD]
[TD="align: right"]11/06/18[/TD]
[/TR]
[TR]
[TD]User 2[/TD]
[TD="align: right"]16:14:44[/TD]
[TD="align: right"]11/06/18[/TD]
[TD="align: right"]16:35:54[/TD]
[TD="align: right"]11/06/18[/TD]
[/TR]
[TR]
[TD]User 2[/TD]
[TD="align: right"]16:36:13[/TD]
[TD="align: right"]11/06/18[/TD]
[TD="align: right"]17:00:49[/TD]
[TD="align: right"]11/06/18[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 608"]
<colgroup><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]
Hopefully I can explain this clearly... I am currently trying to calculate the hours/minutes from Time Range A (which is a recurring schedule of APPROVED time ranges) that do NOT overlap with Time Range B (which is a larger data set of different login periods). Basically, users will have allotted time ranges through out the day that they are allowed to login at, if the time range that they are logged in for is longer or anywhere out side of their allotted time range then I need to be able calculate those hour/minutes. My process right now has many helper columns and some of my equations won't catch accurately calculate what I'm trying to do in all situations.
For example, Time Range A, AKA an approved recurring schedule for the month of November:
User Name Start Time End Time
User 1 0:00 6:45
User 1 9:32 11:45
User 1 19:38 21:35
User 1 20:54 23:59
User 2 6:25 10:00
Time Range B, AKA all the time ranges that users had logged in:
[TABLE="width: 441"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]User Name[/TD]
[TD]Start Time[/TD]
[TD]Start Date[/TD]
[TD]End Time[/TD]
[TD]End Date[/TD]
[/TR]
[TR]
[TD]User 1[/TD]
[TD="align: right"]19:55:14[/TD]
[TD="align: right"]11/02/18[/TD]
[TD="align: right"]20:57:02[/TD]
[TD="align: right"]11/02/18[/TD]
[/TR]
[TR]
[TD]User 1[/TD]
[TD="align: right"]8:41:40[/TD]
[TD="align: right"]11/03/18[/TD]
[TD="align: right"]11:56:10[/TD]
[TD="align: right"]11/03/18[/TD]
[/TR]
[TR]
[TD]User 1[/TD]
[TD="align: right"]11:44:16[/TD]
[TD="align: right"]11/04/18[/TD]
[TD="align: right"]11:52:24[/TD]
[TD="align: right"]11/04/18[/TD]
[/TR]
[TR]
[TD]User 1[/TD]
[TD="align: right"]11:57:18[/TD]
[TD="align: right"]11/04/18[/TD]
[TD="align: right"]12:50:40[/TD]
[TD="align: right"]11/04/18[/TD]
[/TR]
[TR]
[TD]User 1[/TD]
[TD="align: right"]11:07:00[/TD]
[TD="align: right"]11/05/18[/TD]
[TD="align: right"]12:06:02[/TD]
[TD="align: right"]11/05/18[/TD]
[/TR]
[TR]
[TD]User 1[/TD]
[TD="align: right"]8:58:11[/TD]
[TD="align: right"]11/06/18[/TD]
[TD="align: right"]13:09:56[/TD]
[TD="align: right"]11/06/18[/TD]
[/TR]
[TR]
[TD]User 1[/TD]
[TD="align: right"]11:41:48[/TD]
[TD="align: right"]11/06/18[/TD]
[TD="align: right"]13:05:11[/TD]
[TD="align: right"]11/06/18[/TD]
[/TR]
[TR]
[TD]User 1[/TD]
[TD="align: right"]20:12:30[/TD]
[TD="align: right"]11/06/18[/TD]
[TD="align: right"]21:29:57[/TD]
[TD="align: right"]11/06/18[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 441"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]User 1[/TD]
[TD="align: right"]8:54:08[/TD]
[TD="align: right"]11/02/18[/TD]
[TD="align: right"]11:18:19[/TD]
[TD="align: right"]11/02/18[/TD]
[/TR]
[TR]
[TD]User 2[/TD]
[TD="align: right"]9:22:02[/TD]
[TD="align: right"]11/02/18[/TD]
[TD="align: right"]9:31:46[/TD]
[TD="align: right"]11/02/18[/TD]
[/TR]
[TR]
[TD]User 2[/TD]
[TD="align: right"]9:31:55[/TD]
[TD="align: right"]11/02/18[/TD]
[TD="align: right"]10:00:12[/TD]
[TD="align: right"]11/02/18[/TD]
[/TR]
[TR]
[TD]User 2[/TD]
[TD="align: right"]15:46:16[/TD]
[TD="align: right"]11/06/18[/TD]
[TD="align: right"]16:09:16[/TD]
[TD="align: right"]11/06/18[/TD]
[/TR]
[TR]
[TD]User 2[/TD]
[TD="align: right"]16:14:44[/TD]
[TD="align: right"]11/06/18[/TD]
[TD="align: right"]16:35:54[/TD]
[TD="align: right"]11/06/18[/TD]
[/TR]
[TR]
[TD]User 2[/TD]
[TD="align: right"]16:36:13[/TD]
[TD="align: right"]11/06/18[/TD]
[TD="align: right"]17:00:49[/TD]
[TD="align: right"]11/06/18[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 608"]
<colgroup><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]