# Allocating Minutes by Hour of Day

#### IA2022

##### New Member
I have written a formula to allocate minutes for a case duration to each hour (columns E through AB). However, if the case bleeds over into the next day it evaluates 12pm as being < 1am and make the statement false. In the attached spreadsheet, row 2 shows this. Row 3 allocates the minutes correctly. Is there another way to allocate minutes by hour of day?

Test.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAAB
1StatusCase Duration MinutesActual Start Time Actual Out Time 12:00 AM1:00 AM2:00 AM3:00 AM4:00 AM5:00 AM6:00 AM7:00 AM8:00 AM9:00 AM10:00 AM11:00 AM12:00 PM1:00 PM2:00 PM3:00 PM4:00 PM5:00 PM6:00 PM7:00 PM8:00 PM9:00 PM10:00 PM11:00 PM
2Incorrect6111:09:00 PM12:10:00 AM0.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.00
3Correct701:09:00 PM11:10:00 PM0.000.000.000.000.000.000.000.000.000.000.000.000.0051.0060.0060.0060.0060.0060.0060.0060.0060.0060.0010.00
Sheet 1
Cell Formulas
RangeFormula
E2:AB3E2=IF(AND(HOUR(\$C2)=HOUR(E\$1),HOUR(\$D2)=HOUR(E\$1)),\$B2,IF(OR(\$C2>E\$1+(1/24),\$D2<E\$1),0,IF(AND(E\$1>\$C2,E\$1+1/24<\$D2),60,IF(HOUR(E\$1)<=HOUR(\$C2),24*60*(MAX(0,E\$1+(1/24)-\$C2)),24*60*(\$D2-E\$1)))))

### Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
What is the correct result for row 2? Do you just ignore the 10 minutes after 12:00 AM? Or does it wrap around and go in column E?

You do not have a formula in column B. Shouldn't that be a formula? I don't see how row 3 is correct when the duration is actually 601 minutes (10 hours and 1 minute).

What is the correct result for row 2? Do you just ignore the 10 minutes after 12:00 AM? Or does it wrap around and go in column E?

You do not have a formula in column B. Shouldn't that be a formula? I don't see how row 3 is correct when the duration is actually 601 minutes (10 hours and 1 minute).
The correct result for row 2 should read as having 51 minutes allocated to 11pm (column AB) and 10 minutes allocated to 12pm (column E).

That's correct. My apologies. B3 should read 601 minutes. Column B was given in the data set, so there is no formula. Below is the updated data set.

Test.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAAB
1StatusCase Duration MinutesActual Start Time Actual Out Time 12:00 AM1:00 AM2:00 AM3:00 AM4:00 AM5:00 AM6:00 AM7:00 AM8:00 AM9:00 AM10:00 AM11:00 AM12:00 PM1:00 PM2:00 PM3:00 PM4:00 PM5:00 PM6:00 PM7:00 PM8:00 PM9:00 PM10:00 PM11:00 PM
2Incorrect6111:09:00 PM12:10:00 AM0.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.00
3Correct6011:09:00 PM11:10:00 PM0.000.000.000.000.000.000.000.000.000.000.000.000.0051.0060.0060.0060.0060.0060.0060.0060.0060.0060.0010.00
Sheet 1
Cell Formulas
RangeFormula
E2:AB3E2=IF(AND(HOUR(\$C2)=HOUR(E\$1),HOUR(\$D2)=HOUR(E\$1)),\$B2,IF(OR(\$C2>E\$1+(1/24),\$D2<E\$1),0,IF(AND(E\$1>\$C2,E\$1+1/24<\$D2),60,IF(HOUR(E\$1)<=HOUR(\$C2),24*60*(MAX(0,E\$1+(1/24)-\$C2)),24*60*(\$D2-E\$1)))))

The correct result for row 2 should read as having 51 minutes allocated to 11pm (column AB) and 10 minutes allocated to 12pm (column E).

That's correct. My apologies. B3 should read 601 minutes. Column B was given in the data set, so there is no formula. Below is the updated data set.

Test.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAAB
1StatusCase Duration MinutesActual Start Time Actual Out Time 12:00 AM1:00 AM2:00 AM3:00 AM4:00 AM5:00 AM6:00 AM7:00 AM8:00 AM9:00 AM10:00 AM11:00 AM12:00 PM1:00 PM2:00 PM3:00 PM4:00 PM5:00 PM6:00 PM7:00 PM8:00 PM9:00 PM10:00 PM11:00 PM
2Incorrect6111:09:00 PM12:10:00 AM0.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.00
3Correct6011:09:00 PM11:10:00 PM0.000.000.000.000.000.000.000.000.000.000.000.000.0051.0060.0060.0060.0060.0060.0060.0060.0060.0060.0010.00
Sheet 1
Cell Formulas
RangeFormula
E2:AB3E2=IF(AND(HOUR(\$C2)=HOUR(E\$1),HOUR(\$D2)=HOUR(E\$1)),\$B2,IF(OR(\$C2>E\$1+(1/24),\$D2<E\$1),0,IF(AND(E\$1>\$C2,E\$1+1/24<\$D2),60,IF(HOUR(E\$1)<=HOUR(\$C2),24*60*(MAX(0,E\$1+(1/24)-\$C2)),24*60*(\$D2-E\$1)))))

I am continuing to work on this but it's much stickier than it appears at first glance. This would be easier if you used a date/time instead of just the time, but I'm making progress. Will get back when I have time to finish.

I am continuing to work on this but it's much stickier than it appears at first glance. This would be easier if you used a date/time instead of just the time, but I'm making progress. Will get back when I have time to finish.
Thank you!

OK, here is a solution. It's easier to link to the file instead of pasting everything into a post. The formula uses LET which requires 365, but you have that so it should not be an issue.

LET is a new function which allows you to assign values to variables in a formula. This is great to make clear what you are doing and document it right in the formula. If you find this confusing instead of helpful let me know.

OK, here is a solution. It's easier to link to the file instead of pasting everything into a post. The formula uses LET which requires 365, but you have that so it should not be an issue.

LET is a new function which allows you to assign values to variables in a formula. This is great to make clear what you are doing and document it right in the formula. If you find this confusing instead of helpful let me know.

Thanks so much! This worked. Appreciate your help.

Replies
11
Views
368
Replies
2
Views
535
Replies
1
Views
439
Replies
7
Views
710
Replies
1
Views
370

1,221,239
Messages
6,158,717
Members
451,512
Latest member
aimarhu

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