Calculations: Time to Midnight and Time Past Midnight

Toja

New Member
Joined
Dec 1, 2005
Messages
4
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 :confused:
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

Toja

New Member
Joined
Dec 1, 2005
Messages
4
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"))

:rolleyes: Am I off base?
 

Watch MrExcel Video

Forum statistics

Threads
1,118,671
Messages
5,573,583
Members
412,537
Latest member
Mohamed_5966
Top