Allocating Minutes by Hour of Day

[IA2022]

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
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB
1	Status	Case Duration Minutes	Actual Start Time	Actual Out Time	12:00 AM	1:00 AM	2:00 AM	3:00 AM	4:00 AM	5:00 AM	6:00 AM	7:00 AM	8:00 AM	9:00 AM	10:00 AM	11:00 AM	12:00 PM	1:00 PM	2:00 PM	3:00 PM	4:00 PM	5:00 PM	6:00 PM	7:00 PM	8:00 PM	9:00 PM	10:00 PM	11:00 PM
2	Incorrect	61	11:09:00 PM	12:10:00 AM	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00
3	Correct	70	1:09:00 PM	11:10:00 PM	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	51.00	60.00	60.00	60.00	60.00	60.00	60.00	60.00	60.00	60.00	10.00
Sheet 1

Cell Formulas
Range	Formula
E2:AB3	E2	=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]

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]

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
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB
1	Status	Case Duration Minutes	Actual Start Time	Actual Out Time	12:00 AM	1:00 AM	2:00 AM	3:00 AM	4:00 AM	5:00 AM	6:00 AM	7:00 AM	8:00 AM	9:00 AM	10:00 AM	11:00 AM	12:00 PM	1:00 PM	2:00 PM	3:00 PM	4:00 PM	5:00 PM	6:00 PM	7:00 PM	8:00 PM	9:00 PM	10:00 PM	11:00 PM
2	Incorrect	61	11:09:00 PM	12:10:00 AM	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00
3	Correct	601	1:09:00 PM	11:10:00 PM	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	51.00	60.00	60.00	60.00	60.00	60.00	60.00	60.00	60.00	60.00	10.00
Sheet 1

Cell Formulas
Range	Formula
E2:AB3	E2	=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]

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
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB
1	Status	Case Duration Minutes	Actual Start Time	Actual Out Time	12:00 AM	1:00 AM	2:00 AM	3:00 AM	4:00 AM	5:00 AM	6:00 AM	7:00 AM	8:00 AM	9:00 AM	10:00 AM	11:00 AM	12:00 PM	1:00 PM	2:00 PM	3:00 PM	4:00 PM	5:00 PM	6:00 PM	7:00 PM	8:00 PM	9:00 PM	10:00 PM	11:00 PM
2	Incorrect	61	11:09:00 PM	12:10:00 AM	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00
3	Correct	601	1:09:00 PM	11:10:00 PM	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	51.00	60.00	60.00	60.00	60.00	60.00	60.00	60.00	60.00	60.00	10.00
Sheet 1

Cell Formulas
Range	Formula
E2:AB3	E2	=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]

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]

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]

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.

Dropbox

www.dropbox.com

 

[IA2022]

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.

Dropbox

www.dropbox.com

Thanks so much! This worked. Appreciate your help.