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>
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Sort by date Sort by votes
Hi and welcome to the MrExcel message Board.

We can help with the Excel part but you will need to tell us how to solve the problem. For instance, if we look at the first line of your data, how would we determine how many hours are between 9:00pm and 8:00 am for Monday?
 
Upvote 0
Total Hours of Operations per location per week - then how many operate between 9:00 pm - 8:00 am

Hi,

I have this table that I put together for to calculate the numbers of hours that each one of my locations is open. First column is the location, then the day of the week with Open and Close hour, there are several that are open 24 hours a day, thus have the Open hour as 12:00 AM and Close hour as 12:00 am.

Now I want to tally the hours of operations for the week for each location. Then, I want to sum up the hours for the locations that are open between 9:00pm - 8:00 am. This is my ultimate goal, to know how many locations open during 9:00pm - 8:00 am so that I can analyze their sales during those hours.

LocationMondayMondayTuesdayTuesdayWednesdayWednesdayThursdayThursdayFridayFridaySaturdaySaturdaySundaySundayTOTAL
OpenCloseOpenCloseOpenCloseOpenCloseOpenCloseOpenCloseOpenCloseHRS OPS
16: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
26: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 AM81:0
36: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
46: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 AM96:0
512: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
612: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
76: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 AM97:0
812: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
96: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 AM313:0
106: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 AM80:0
116: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 AM78:0
1212: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

<tbody>
</tbody>
 
Upvote 0
Re: Total Hours of Operations per location per week - then how many operate between 9:00 pm - 8:00 am

Hi ITExec
I think its an interesting puzzle. I had a go at this before but then realised it was taking me longer than I thought to work it all out. I defininitely found some useful excel functions by googling about. I'll have a another go and let you know what formula I come up with ---- no promises that I can get it working though :)
Cheers
M
 
Upvote 0
Row\Col
A​
B​
C​
D​
E​
F​
G​
H​
I​
J​
K​
L​
M​
N​
O​
P​
Q​
1​
Location​
Mon​
Mon​
Tue​
Tue​
Wed​
Wed​
Thu​
Thu​
Fri​
Fri​
Sat​
Sat​
Sun​
Sum​
Total​
TOTAL​
2​
Open​
Close​
Open​
Close​
Open​
Close​
Open​
Close​
Open​
Close​
Open​
Close​
Open​
Close​
Hours​
9P-8A​
3​
1​
6:00​
23:00​
6:00​
23:00​
6:00​
23:00​
6:00​
23:00​
0:00​
24:00​
0:00​
24:00​
7:00​
23:00​
132:00​
16:00​
4​
2​
6:00​
24:00​
6:00​
24:00​
6:00​
24:00​
6:00​
26:00​
0:00​
24:00​
0:00​
24:00​
7:00​
24:00​
139:00​
23:00​

The open and closing times are formatted as [h]:mm. I changed a closing time of 0:00 to 24:00, and a 2 AM closing to 26:00.

The formula in P2 is

=SUMPRODUCT(($B$2:$O$2="Close") * $B3:$O3 - ($B$2:$O$2="Open") * $B3:$O3)

The formula in Q2, which MUST be confirmed with Ctrl+Shif+Enter instead of just Enter, is

=SUMPRODUCT(
($B$2:$O$2="Close") * IF($B3:$O3 - "21:00" < 0, 0, $B3:$O3 - "21:00") -
($B$2:$O$2="Open") * IF($B3:$O3 - "21:00" < 0, 0, IF($B3:$O3 - "21:00" - "08:00" > 0, "08:00", $B3:$O3 - "21:00")))
 
Upvote 0
To avoid too much complication, I chose to solve this "kindergarten" style....

I would write a VBA subroutine, create an array of 24 elements. Each element represents one hour of time. Then I would populate the hours the shop is open with say the word "Open", then write blanks to the elements that are outside the 9pm , then total how many element still hold "Open". Repeat for all the other days of the week and shops and total it all up at the end. This is just another way to think about it I guess!!
 
Upvote 0
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
87
ChetanPuri
C
M
  • Solved
Returning results based on multiple criteria
Replies
5
Views
292
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
174
XLfriend777
XLfriend777
R
  • Solved
vlookup, INDEX, or SUMPRODUCT???
2
Replies
10
Views
350
KRice
K
M
  • Solved
Sumifs Formula not working
2
Replies
15
Views
836
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,215,429
Messages
6,124,840
Members
449,193
Latest member
MikeVol

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