Sum total hours "when in operation" between 9:00pm and 8:00 am

I

ItExec

New Member
Joined
Oct 25, 2014
Messages
2
Basically, I am able to calculate the total hours in operations. Now, I need to sum the hours in Operations that fall between 9:00pm and 8:00 am.
MondayTuesdayWednesdayThursdayFridaySaturdaySunday TOTAL
Open CloseOpen CloseOpen CloseOpen CloseOpen CloseOpen CloseOpen CloseHRS OPS
6:00:00 AM11:00:00 PM6:00:00 AM11:00:00 PM6:00:00 AM11:00:00 PM6:00:00 AM11:00:00 PM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM7:00:00 AM11:00:00 PM194:0
6:00:00 AM12:00:00 AM6:00:00 AM12:00:00 AM6:00:00 AM12:00:00 AM6:00:00 AM2:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM7:00:00 AM12:00:00 AM129:0
6:00:00 AM2:00:00 AM6:00:00 AM2:00:00 AM6:00:00 AM2:00:00 AM6:00:00 AM2:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM6:00:00 AM2:00:00 AM88:0
6:00:00 AM12:00:00 AM6:00:00 AM12:00:00 AM6:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM6:00:00 AM12:00:00 AM144:0
12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM168:0
12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM168:0
6:00:00 AM12:00:00 AM6:00:00 AM12:00:00 AM6:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM7:00:00 AM12:00:00 AM145:0
12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM168:0
6:00:00 AM12:00:00 AM6:00:00 AM12:00:00 AM6:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM7:00:00 AM12:00:00 AM145:0
6:00:00 AM12:00:00 AM6:00:00 AM12:00:00 AM6:00:00 AM12:00:00 AM6:00:00 AM2:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM6:00:00 AM12:00:00 AM128:0
6:00:00 AM12:00:00 AM6:00:00 AM12:00:00 AM6:00:00 AM12:00:00 AM6:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM6:00:00 AM12:00:00 AM138:0
12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM168:0
12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM168:0
6:00:00 AM11:00:00 PM6:00:00 AM11:00:00 PM6:00:00 AM11:00:00 PM6:00:00 AM12:00:00 AM6:00:00 AM1:00:00 AM6:00:00 AM1:00:00 AM7:00:00 AM11:00:00 PM149:0
12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM168:0
6:30:00 AM11:00:00 PM6:30:00 AM11:00:00 PM6:30:00 AM11:00:00 PM6:30:00 AM11:00:00 PM6:30:00 AM1:00:00 AM6:30:00 AM1:00:00 AM7:00:00 AM11:00:00 PM163:0
6:00:00 AM11:00:00 PM6:00:00 AM11:00:00 PM6:00:00 AM11:00:00 PM6:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM6:00:00 AM12:00:00 AM171:0
6:00:00 AM12:00:00 AM6:00:00 AM12:00:00 AM6:00:00 AM12:00:00 AM6:00:00 AM12:00:00 AM6:00:00 AM1:00:00 AM7:00:00 AM1:00:00 AM7:00:00 AM12:00:00 AM106:0
12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM168:0
6:00:00 AM12:00:00 AM6:00:00 AM12:00:00 AM6:00:00 AM12:00:00 AM6:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM6:00:00 AM1:00:00 AM127:0
7:00:00 AM11:00:00 PM7:00:00 AM11:00:00 PM7:00:00 AM11:00:00 PM7:00:00 AM11:00:00 PM7:00:00 AM1:00:00 AM7:00:00 AM1:00:00 AM9:00:00 AM11:00:00 PM168:0
7:00:00 AM11:00:00 PM7:00:00 AM11:00:00 PM7:00:00 AM11:00:00 PM7:00:00 AM11:00:00 PM7:00:00 AM1:00:00 AM7:00:00 AM1:00:00 AM10:00:00 AM11:00:00 PM169:0
6:30:00 AM12:00:00 AM6:30:00 AM12:00:00 AM6:30:00 AM12:00:00 AM6:30:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM6:30:00 AM12:00:00 AM140:30
6:00:00 AM10:00:00 PM6:00:00 AM10:00:00 AM6:00:00 AM10:00:00 AM6:00:00 AM10:00:00 AM6:00:00 AM1:00:00 AM6:00:00 AM1:00:00 AM8:00:00 AM10:00:00 AM108:0
5:30:00 AM1:00:00 AM5:30:00 AM1:00:00 AM5:30:00 AM1:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM5:30:00 AM1:00:00 AM98:0
5:00:00 AM1:00:00 AM5:00:00 AM1:00:00 AM5:00:00 AM1:00:00 AM5:00:00 AM1:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM6:00:00 AM1:00:00 AM79:0
6:00:00 AM11:00:00 PM6:00:00 AM11:00:00 PM6:00:00 AM11:00:00 PM6:00:00 AM11:00:00 PM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM7:00:00 AM11:00:00 PM194:0
6:00:00 AM12:00:00 AM6:00:00 AM12:00:00 AM6:00:00 AM12:00:00 AM6:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM6:00:00 AM12:00:00 AM138:0
5:00:00 AM11:30:00 PM5:00:00 AM11:30:00 PM5:00:00 AM11:30:00 PM5:00:00 AM11:30:00 PM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM5:00:00 AM11:30:00 PM190:30
12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM168:0
6:00:00 AM11:00:00 PM6:00:00 AM11:00:00 PM6:00:00 AM11:00:00 PM6:00:00 AM11:00:00 PM6:00:00 AM2:00:00 AM6:00:00 AM2:00:00 AM6:00:00 AM11:00:00 PM161:0
7:00:00 AM11:00:00 PM7:00:00 AM11:00:00 PM7:00:00 AM11:00:00 PM7:00:00 AM11:00:00 PM7:00:00 AM1:00:00 AM7:00:00 AM1:00:00 AM8:00:00 AM11:00:00 PM167:0
6:30:00 AM11:00:00 PM6:30:00 AM11:00:00 PM6:30:00 AM11:00:00 PM6:30:00 AM11:00:00 PM6:30:00 AM12:00:00 AM6:30:00 AM12:00:00 AM8:00:00 AM11:00:00 PM186:0
6:30:00 AM11:00:00 PM6:30:00 AM11:00:00 PM6:30:00 AM11:00:00 PM6:30:00 AM11:00:00 PM6:30:00 AM12:00:00 AM6:30:00 AM12:00:00 AM6:30:00 AM11:00:00 PM184:30
6:30:00 AM11:00:00 PM6:30:00 AM11:00:00 PM6:30:00 AM11:00:00 PM6:30:00 AM11:00:00 PM6:30:00 AM12:00:00 AM6:30:00 AM12:00:00 AM6:30:00 AM11:00:00 PM184:30
7:00:00 AM10:00:00 PM7:00:00 AM10:00:00 AM7:00:00 AM10:00:00 AM7:00:00 AM10:00:00 AM7:00:00 AM10:00:00 AM7:00:00 AM10:00:00 AM10:00:00 AM10:00:00 AM134:0
6:00:00 AM12:00:00 AM6:00:00 AM12:00:00 AM6:00:00 AM12:00:00 AM6:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM6:00:00 AM12:00:00 AM138:0
7:00:00 AM11:00:00 PM7:00:00 AM11:00:00 PM7:00:00 AM11:00:00 PM7:00:00 AM11:00:00 PM7:00:00 AM12:00:00 AM7:00:00 AM12:00:00 AM7:00:00 AM11:00:00 PM188:0
12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM168:0
6:00:00 AM11:00:00 PM6:00:00 AM11:00:00 PM6:00:00 AM11:00:00 PM6:00:00 AM11:00:00 PM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM6:00:00 AM11:00:00 PM193:0
6:00:00 AM12:00:00 AM6:00:00 AM12:00:00 AM6:00:00 AM12:00:00 AM6:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM6:00:00 AM12:00:00 AM138:0
7:00:00 AM3:00:00 AM7:00:00 AM3:00:00 AM7:00:00 AM3:00:00 AM7:00:00 AM3:00:00 AM7:00:00 AM3:00:00 AM7:00:00 AM3:00:00 AM10:00:00 AM3:00:00 AM73:0
6:00:00 AM12:00:00 AM6:00:00 AM12:00:00 AM6:00:00 AM12:00:00 AM6:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM6:00:00 AM12:00:00 AM138:0
7:00:00 AM11:00:00 PM7:00:00 AM11:00:00 PM7:00:00 AM11:00:00 PM7:00:00 AM11:00:00 PM7:00:00 AM12:00:00 AM7:00:00 AM12:00:00 AM8:00:00 AM11:00:00 PM189:0
7:00:00 AM10:00:00 PM7:00:00 AM10:00:00 AM7:00:00 AM10:00:00 AM7:00:00 AM10:00:00 AM7:00:00 AM12:00:00 AM7:00:00 AM12:00:00 AM8:00:00 AM10:00:00 AM136:0
6:00:00 AM12:00:00 AM6:00:00 AM12:00:00 AM6:00:00 AM12:00:00 AM6:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM6:00:00 AM12:00:00 AM138:0
6:00:00 AM12:00:00 AM6:00:00 AM12:00:00 AM6:00:00 AM12:00:00 AM6:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM6:00:00 AM11:00:00 AM137:0
7:00:00 AM10:00:00 PM7:00:00 AM10:00:00 AM7:00:00 AM10:00:00 AM7:00:00 AM10:00:00 AM7:00:00 AM12:00:00 AM7:00:00 AM12:00:00 AM8:00:00 AM10:00:00 AM136:0
7:00:00 AM12:00:00 AM7:00:00 AM12:00:00 AM7:00:00 AM12:00:00 AM7:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM7:00:00 AM12:00:00 AM143:0
6:00:00 AM12:00:00 AM6:00:00 AM12:00:00 AM6:00:00 AM12:00:00 AM6:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM6:00:00 AM12:00:00 AM138:0
6:00:00 AM11:00:00 PM6:00:00 AM11:00:00 PM6:00:00 AM11:00:00 PM6:00:00 AM11:00:00 PM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM6:00:00 AM11:00:00 PM193:0
12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM168:0
6:00:00 AM12:00:00 AM6:00:00 AM12:00:00 AM6:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM6:00:00 AM12:00:00 AM144:0
6:00:00 AM12:00:00 AM6:00:00 AM12:00:00 AM6:00:00 AM12:00:00 AM6:00:00 AM12:00:00 AM6:00:00 AM12:00:00 AM7:00:00 AM12:00:00 AM7:00:00 AM12:00:00 AM128:0
6:00:00 AM11:00:00 PM6:00:00 AM11:00:00 PM6:00:00 AM11:00:00 PM6:00:00 AM11:00:00 PM6:00:00 AM12:00:00 AM6:00:00 AM12:00:00 AM7:00:00 AM11:00:00 PM182:0
12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM168:0
6:00:00 AM11:00:00 PM6:00:00 AM11:00:00 PM6:00:00 AM11:00:00 PM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM6:00:00 AM11:00:00 PM188:0
6:00:00 AM1:00:00 AM6:00:00 AM1:00:00 AM6:00:00 AM1:00:00 AM6:00:00 AM1:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM6:00:00 AM2:00:00 AM84:0
6:00:00 AM11:00:00 PM6:00:00 AM11:00:00 PM6:00:00 AM11:00:00 PM6:00:00 AM11:00:00 PM6:00:00 AM1:00:00am6:00:00 AM1:00:00am7:30:00 AM11:00:00 PM158:30
6:00:00 AM11:00:00 PM6:00:00 AM11:00:00 PM6:00:00 AM11:00:00 PM6:00:00 AM1:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM6:00:00 AM11:00:00 PM171:0
6:00:00 AM11:00:00 PM6:00:00 AM11:00:00 PM6:00:00 AM11:00:00 PM6:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM6:00:00 AM11:00:00 PM182:0
7:00:00 AM11:00:00 PM7:00:00 AM11:00:00 PM7:00:00 AM11:00:00 PM7:00:00 AM11:00:00 PM7:00:00 AM12:00:00 AM7:00:00 AM12:00:00 AM8:00:00 AM11:00:00 PM189:0
7:00:00 AM12:00:00 AM7:00:00 AM12:00:00 AM7:00:00 AM12:00:00 AM7:00:00 AM12:00:00 AM7:00:00 AM1:00:00 AM7:00:00 AM1:00:00 AM7:00:00 AM12:00:00 AM111:0
6:00:00 AM11:00:00 PM6:00:00 AM11:00:00 PM6:00:00 AM11:00:00 PM6:00:00 AM11:00:00 PM12:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AM6:00:00 AM11:00:00 PM193:0
7:00:00 AM11:00:00 PM7:00:00 AM11:00:00 PM7:00:00 AM11:00:00 PM7:00:00 AM11:00:00 PM7:00:00 AM1:00:00 AM7:00:00 AM1:00:00 AM8:00:00 AM11:00:00 PM167:0

<colgroup><col width="87" style="width: 65pt; mso-width-source: userset; mso-width-alt: 3181; mso-outline-level: 1;"> <col width="87" style="width: 65pt; mso-width-source: userset; mso-width-alt: 3181; mso-outline-level: 1;"> <col width="87" style="width: 65pt; mso-width-source: userset; mso-width-alt: 3181; mso-outline-level: 1;"> <col width="87" style="width: 65pt; mso-width-source: userset; mso-width-alt: 3181; mso-outline-level: 1;"> <col width="87" style="width: 65pt; mso-width-source: userset; mso-width-alt: 3181; mso-outline-level: 1;"> <col width="87" style="width: 65pt; mso-width-source: userset; mso-width-alt: 3181; mso-outline-level: 1;"> <col width="89" style="width: 67pt; mso-width-source: userset; mso-width-alt: 3254; mso-outline-level: 1;"> <col width="89" style="width: 67pt; mso-width-source: userset; mso-width-alt: 3254; mso-outline-level: 1;"> <col width="87" style="width: 65pt; mso-width-source: userset; mso-width-alt: 3181; mso-outline-level: 1;"> <col width="87" style="width: 65pt; mso-width-source: userset; mso-width-alt: 3181; mso-outline-level: 1;"> <col width="87" style="width: 65pt; mso-width-source: userset; mso-width-alt: 3181; mso-outline-level: 1;"> <col width="87" style="width: 65pt; mso-width-source: userset; mso-width-alt: 3181; mso-outline-level: 1;"> <col width="89" style="width: 67pt; mso-width-source: userset; mso-width-alt: 3254; mso-outline-level: 1;"> <col width="89" style="width: 67pt; mso-width-source: userset; mso-width-alt: 3254; mso-outline-level: 1;"> <col width="82" style="width: 62pt; mso-width-source: userset; mso-width-alt: 2998;"> <tbody>
</tbody>
 
Sort by date Sort by votes
I straightened mine out to make it more like a database table. A copy can be found here: https://onedrive.live.com/edit.aspx?cid=681493C8FF2672D3&resid=681493C8FF2672D3!2692&app=Excel

I also changed the hours into days and hours based on next week. So start and finish times of 12:00 AM will have a different date to make sure the calculation is correct.

Individual locations can be filtered using the AutoFilter. Remember to select the location number AND Blanks so that the totals will be displayed.

Hours before 8:00 AM and after 9:00 PM are calculated separately then summed. It makes for easier error checking.

Total hours worked are also calculated.

I woinder if we will all get the same answer?
 
Last edited:
Upvote 0

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
You must log in or register to reply here.

Similar threads

C
  • Question
Calculate and Identify Ordinary Hours, Double time or 1.5 Time
Replies
0
Views
108
ChetanPuri
C
M
  • Solved
Returning results based on multiple criteria
Replies
5
Views
313
DanteAmor
DanteAmor
K
  • Question
Help with difference between 2 dates including am/pm and check multiple entries to see if they occur at the same date and am/pm
Replies
1
Views
197
XLfriend777
XLfriend777
R
  • Solved
vlookup, INDEX, or SUMPRODUCT???
2
Replies
10
Views
418
KRice
K
M
  • Solved
Sumifs Formula not working
2
Replies
15
Views
889
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,216,077
Messages
6,128,684
Members
449,463
Latest member
Jojomen56

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top