MAX Value based on Time range- not working correctly

[InstructorAmberA]

row/column	C	D	E	--->	BD ---->	BI
11	Start	End	Target		TimeHour	Target
12	3:00 AM	6:00 AM	2		12:00 AM	1
13	1:00 AM	6:00 AM	4		1:00 AM	4
14	4:00 AM	6:00 AM	2		2:00 AM	3
15	4:00 AM	7:00 AM	3		3:00 AM	2
16	1:00 AM	5:00 AM	4		4:00 AM	2
17	9:00 PM	12:00 AM	5		5:00 AM	2
|					6:00 AM	3
|					keeps going hour by hour
35					11:00 PM	5

<tbody>
</tbody>

---

Goal: Make the Red cells calculate themselves. Max TARGET (BI) based On block of time shown in C to D .

Range BD12-BD35 (TimeHour)= Hours of the day (1 cell per hour)
BI12-BI35 (Target) are the Targets for those hours individually. Columns BE-BH have other data not relevant to this formula.

E12 -E17 is filled out manually. It is the highest (Target) number in the range of time shown. Example 3am, 4am, 5am hours are target 2, 2, 2, so give me E12=2
Row 16 - 1AM to 5AM, 1AM=4, 2AM = 3, 3AM=3, 4AM=2 so highest target is 4, give me 4.

{=MAX(IF($BD$12:$BD$40>=C12,IF($BD$12:$BD$40=D12,$BI$12:$BI$40)))} is giving me 0 in every cell. I did use CSE to enter it and it provided the { }. I typed them here for your reference.

Bad Result = 0 for everything. I originally had the table BD-BI on another worksheet called Sheet5 in this formula. I copied it over to the main page to see if it would change, but still zeros.


=MAX(IF((Sheet5!$A$4:$A$27<=D12)*(Sheet5!$A$4:$A$27>=C12),Sheet5!$F$4:$F$27,""))


https://www.mrexcel.com/forum/excel-questions/509384-find-max-min-value-based-time-criteria.html

I found this article above and was trying to use it to create/fix but I am still missing something. Any help would be appreciated.

 

[KolGuyXcel]

Try (as array formula)

=MAX(IF($BD$12:$BD$40>=C12,IF($BD$12:$BD$40<D12,$BI$12:$BI$40)))

 

[KolGuyXcel]

It would still give an error with any window ending at midnight i.e. 12AM. So for such windows, all such 12 AM in column D may be replaced with 11:59 PM.

 

[InstructorAmberA]

Thank you KolGuyXcel That worked.
Just for future if anyone is seeing this post - I had another problem that I just realized but am not sure how I caused.
Each of the time columns was manually typed. And each only shows Hour, minutes AM/PM like above.

After further hunting for why it wasn't working I found in the formula bar - One (Column BD) was showing 3:00:00 while the others (C and D)were showing 3:00 Without the seconds. Even with the change in formula, it would not work until the seconds got manually added to BD

 

[InstructorAmberA]

Technically, ending at 12am only needs to count the targets of the hour before (11pm).
Each of these can really disregard the end time column.... is there a way to make it look at Start hour to End hour minus 59 minutes ?

Line 14 for example -
4am to 6am is really getting the target for the 4am and 5am hours. 6am itself is irrelevant, just that it ends there and won't go further.
Technically the time should be 4am to 5:59am but I will have issue with sending out schedules if I change them to show that. Can the formula assume it instead?