Conference Room Reservation Program - 7 pm is not showing for some cells. Thank you.

[zander140]

Hi, I attached a "Conference Room Reservation Program". Please see below. I am having the following problem: For Conference Room 1, it should show booking from 7 pm to 9 pm like so. However, this formula is only showing the correct booking for any booking from 7 pm when F$6 is equal to 5:00 - 12:00 and 2:00-23:00 and anything else it shows only booking for 8 pm - 9 pm even though the booking should have been from 7 pm - 9 pm (in this case). You can click on the scrollbar for "Time Step" to see this change.

Can you please help show the 7 pm when F$6  = 0:00 and when the booking starts from 7pm. When the "Time Step" to see this changes, it should always show the correct booking so it should show 7 pm - 9 pm. Thank you. *I described this in the picture below.

Here is the array formula used. Thank you.

=1-SUMPRODUCT(('Conference Room Reservation'!$C$2:$C$1000<=Z$6)*('Conference Room Reservation'!$B$2:$B$1000=$I$3)*('Conference Room Reservation'!$D$2:$D$1000>=Z$6)*('Conference Room Reservation'!$E$2:$E$1000=$C7))

Any help is appreciated. Thank you.

[#2]

@zander140 

The problem clearly has something to do with the fact that you're specifying exact boundaries on the start and end times, but the math involved in all the related calculations is not uniformly as exact at that level. All I have to do to get it to show 7 p.m as the start time is change the entry  in the tab Conference Room Reservation to 6:59 p.m. (18:59) and it displays with the meeting starting at 7:00. Similarly, if I end that reservation at 20:00, it shows it running to 9 p.m., but if I change the ending time to 19:59 it shows as ending at 8:00 p.m.

My hypothesis is that the fraction that represents minutes--it IS a fraction--gets rounded in a such a way that your very precise boundary specs for start and finish in the graphic display are on the "wrong side" of that line. It could also be the formulas that calculate the numbers in row 6 of the "Availability" tab, numbers that you then use as the basis for comparison in the SUMPRODUCT formula. 

My suggestion, therefore: consider building a "fudge factor" into your formulas such that you still enter 19:00 for a start but it gets handled "as if" it's one minute earlier; similarly, the end time gets entered as 21:00 but is handled "as if" it's one minute earlier. OR revise the formulas in the conditional formatting rules to allow for a fudge factor of one minute on either side of the stated start and finish times.