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:
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

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?
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,019
Messages
5,834,980
Members
430,331
Latest member
Syed Yasir Hannan

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
Top