Calculations: Time to Midnight and Time Past Midnight

[Toja]

Stuck on calculation. We have courier routes that we want to look at distribuion around certain time increments. Our PM Routes run through midnight. The time increment I'm having trouble with is from 23:31 to 0:00 (11:31:00 PM to 12:00:59 AM)
I have a table where individuals will enter whether the route is an AM route or a PM route - then I have a sumproduct that SHOULD look at the return time and if it falls betwee 23:31:00 and 00:00:59 - then it should return the number of routes that return in that time period. Following is the formula in the table to count the number of routes
=SUMPRODUCT(--($G$7:$G$80>=$M47),--($G$7:$G$80<=$N47),--($B$7:$B$80="PM"))

Where Column G is the Return Time
Column B is the AM or PM Designation
M47 = 11:31:00 PM (or 23:31:00)
N57 = 12:00:59 AM (or 00:00:59)

I have a route that returns at 11:59:00 PM (23:59:00) and the formula noted above does not show a "1"

Any assistance would be greatly appreciated

 

[Toja]

Sorry for multiple posts....I think this may solve it - where I make the first part of the return time greater than the last part of the return time.

=SUMPRODUCT(--($G$7:$G$80>=$M47),--($G$7:$G$80<=($M47>$N47)),--($B$7:$B$80="PM"))

Am I off base?