Calculate Peak / Off Peak Times

[marty31]

Hi,
I am trying to work out necessary calculation for spreadsheet below.
Off Peak Times are between 7 and 11am. I need total amount of hours as "Peak" hours in from the first column. The result for the first one would be 4 hours.
Any help would be greatly appreciated.
I am using office 2003.

Thanks in advance

<TABLE style="WIDTH: 138pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=183 x:str><COLGROUP><COL style="WIDTH: 46pt; mso-width-source: userset; mso-width-alt: 2230" span=3 width=61><TBODY><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; WIDTH: 92pt; HEIGHT: 13.5pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: black 1pt solid" class=xl28 height=18 width=122 colSpan=2>Monday</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; WIDTH: 46pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl30 rowSpan=3 width=61>Peak Hours</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 13.5pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl25 height=18 x:str="Start ">Start </TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl25 x:str="End ">End </TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 13.5pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 1pt solid" class=xl24 height=18>eg. 7.5</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 1pt solid" class=xl24>e.g. 23</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl26 height=17 align=right x:num>6</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl26 align=right x:num>17.5</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl26> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 1pt solid" class=xl27 height=17 align=right x:num>6</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 1pt solid" class=xl27 align=right x:num>17.5</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 1pt solid" class=xl27> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 1pt solid" class=xl27 height=17 align=right x:num>5</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 1pt solid" class=xl27 align=right x:num>18</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 1pt solid" class=xl27> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 1pt solid" class=xl27 height=17 align=right x:num>6</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 1pt solid" class=xl27 align=right x:num>21</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 1pt solid" class=xl27> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 1pt solid" class=xl27 height=17 align=right x:num>0</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 1pt solid" class=xl27 align=right x:num>24</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 1pt solid" class=xl27> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 1pt solid" class=xl27 height=17 align=right x:num>4.5</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 1pt solid" class=xl27 align=right x:num>23</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 1pt solid" class=xl27> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 1pt solid" class=xl27 height=17 align=right x:num>7</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 1pt solid" class=xl27 align=right x:num>20</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 1pt solid" class=xl27> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 1pt solid" class=xl27 height=17 align=right x:num>7</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 1pt solid" class=xl27 align=right x:num>20</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 1pt solid" class=xl27> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 1pt solid" class=xl27 height=17 align=right x:num>7</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 1pt solid" class=xl27 align=right x:num>20</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 1pt solid" class=xl27> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 1pt solid" class=xl27 height=17 align=right x:num>6</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 1pt solid" class=xl27 align=right x:num>23</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 1pt solid" class=xl27> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 1pt solid" class=xl27 height=17 align=right x:num>0</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 1pt solid" class=xl27 align=right x:num>24</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 1pt solid" class=xl27> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 1pt solid" class=xl27 height=17 align=right x:num>0</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 1pt solid" class=xl27 align=right x:num>24</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 1pt solid" class=xl27> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 1pt solid" class=xl27 height=17 align=right x:num>0</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 1pt solid" class=xl27 align=right x:num>24</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 1pt solid" class=xl27> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 1pt solid" class=xl27 height=17 align=right x:num>0</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 1pt solid" class=xl27 align=right x:num>24</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 1pt solid" class=xl27> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 1pt solid" class=xl27 height=17 align=right x:num>6</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 1pt solid" class=xl27 align=right x:num>23</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 1pt solid" class=xl27> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 1pt solid" class=xl27 height=17 align=right x:num>7</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 1pt solid" class=xl27 align=right x:num>20</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 1pt solid" class=xl27> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 1pt solid" class=xl27 height=17 align=right x:num>7</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 1pt solid" class=xl27 align=right x:num>20</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 1pt solid" class=xl27> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 1pt solid" class=xl27 height=17 align=right x:num>7</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 1pt solid" class=xl27 align=right x:num>20</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 1pt solid" class=xl27> </TD></TR></TBODY></TABLE>

 

[Jon von der Heyden]

Assuming the times begin in row 4, with start in col A and end in col B:

=MIN(11,B4)-MAX(A4,7)

 

[marty31]

Thank you for your help. Huge time saver.
Cheers

 

[Hound3821]

Hi I have a similar dilemma but it's a little more complex. I have a series of start and stop times. the times can occur any time in the 24 hr period. What I want to do is calculate how much time is during off-peak and how much time is during peak. See example below

motor starts motor stops
Session 1 11:30 PM 12:45 AM
Session 2 06:00 AM 11:30 PM
Session 3 11:30 PM 08:15 AM
Session 4 04:15 AM 07:30 AM

If we assume peak is from 7am to 11pm then we would have the following results:

Session 1 = 1:15 off-peak 22:45 peak
Session 2 = 1:30 off-peak 22:30 peak
Session 3 = 7:30 off-peak 16:30 peak
Session 4 = 3:15 off-peak 20:45 peak

As you can see the motor can start during peak or off-peak times and finish during peak or off-peak times. How can i get Excel to calculate the correct values given the variability of start and stop times?

Many thanks