Allocating Minutes by Hour of Day

IA2022

New Member
Joined
May 4, 2022
Messages
6
Office Version
  1. 365
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

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

6StringJazzer

Well-known Member
Joined
Jan 27, 2010
Messages
2,384
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
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).
 

IA2022

New Member
Joined
May 4, 2022
Messages
6
Office Version
  1. 365
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)))))
 

IA2022

New Member
Joined
May 4, 2022
Messages
6
Office Version
  1. 365
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)))))
 

6StringJazzer

Well-known Member
Joined
Jan 27, 2010
Messages
2,384
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
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.
 

IA2022

New Member
Joined
May 4, 2022
Messages
6
Office Version
  1. 365
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!
 

6StringJazzer

Well-known Member
Joined
Jan 27, 2010
Messages
2,384
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
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.

 
Solution

IA2022

New Member
Joined
May 4, 2022
Messages
6
Office Version
  1. 365
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.
 

Forum statistics

Threads
1,175,890
Messages
5,900,086
Members
434,817
Latest member
nbretscher13

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