# Calculations: Time to Midnight and Time Past Midnight

#### Toja

##### New Member
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

### Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
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?

Replies
8
Views
149
Replies
1
Views
324
Replies
11
Views
1K
Replies
4
Views
51
Replies
3
Views
133

1,211,802
Messages
6,104,061
Members
447,892
Latest member

### 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.

### Which adblocker are you using?

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

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