Calendar is not Highlighting Accurate Time

Question

I have been utilizing this calendar and have observed that it is not functioning correctly, as it occasionally highlights incorrect time entries.

For instance, in a sheet titled "Data" containing meeting information, I have identified a row where the duration of time is not accurately highlighted. For example, if the meeting is scheduled from 6:00 PM to 6:30 PM, the calendar should highlight the specific date from 6:00 PM to 6:30 PM. However, it only highlights the cell for 6:00 PM whic is weired.

I hope someone can help or suggest a better ida to fix this. Thanks

Data Sheet

Calendar sheet

I have attached a file link too to check the workbook File Link

Formula that i have using below formula, I have removed the Meeting Formula it only contains to highlight only the deadlines.

=IFERROR(
Control!$D$14&INDEX(tab_calendar_data[Meeting / Deadline],
MATCH(
LARGE(
(tab_calendar_data[Type]=Control!$C$14)*(tab_calendar_data[Start Time]<=$B7)*(tab_calendar_data[End Time]>=$B7)*(tab_calendar_data[Start Date]<=C$6)*(tab_calendar_data[End Date]>=C$6)*
(((WEEKDAY(C$6)<>1)*(WEEKDAY(C$6)<>7)*(tab_calendar_data[Cycle]="daily"))+
(((WEEKDAY(tab_calendar_data[Start Date])=WEEKDAY(C$6))*(tab_calendar_data[Cycle]="weekly"))+
((MOD(C$6-tab_calendar_data[Start Date],14)=0)*(tab_calendar_data[Cycle]="bi-weekly"))+
((MOD(C$6-tab_calendar_data[Start Date],28)=0)*(tab_calendar_data[Cycle]="monthly"))+
((tab_calendar_data[Start Date]=C$6)*(tab_calendar_data[Cycle]="one-time"))))
*tab_calendar_data[ID],1),tab_calendar_data[ID],
0)),"")

Answer 1

Technically, this isn't an answer since it's addressing the question in your comment, rather than the original question...

=IFERROR(
Control!$D$14&INDEX(tab_calendar_data[Meeting / Deadline],
MATCH(
LARGE(
(tab_calendar_data[Type]=Control!$C$14)*(ROUND(tab_calendar_data[Start Time],8)<=ROUND($B7,8))*(ROUND(tab_calendar_data[End Time],8)>=ROUND($B7,8))*(tab_calendar_data[Start Date]<=C$6)*(tab_calendar_data[End Date]>=C$6)*
(((WEEKDAY(C$6)<>1)*(WEEKDAY(C$6)<>7)*(tab_calendar_data[Cycle]="daily"))+
(((WEEKDAY(tab_calendar_data[Start Date])=WEEKDAY(C$6))*(tab_calendar_data[Cycle]="weekly"))+
((MOD(C$6-tab_calendar_data[Start Date],14)=0)*(tab_calendar_data[Cycle]="bi-weekly"))+
((MOD(C$6-tab_calendar_data[Start Date],28)=0)*(tab_calendar_data[Cycle]="monthly"))+
((tab_calendar_data[Start Date]=C$6)*(tab_calendar_data[Cycle]="one-time"))))
*tab_calendar_data[ID],1),tab_calendar_data[ID],
0)),"")

The roundings are necessary because, in the example of E30 on the calendar sheet, the tab_calendar_data[End Time]>=$B30 evaluates to a sequence of FALSE values because the latest [End Time], i.e. 0.770833333333333 is never (quite) equal to the value in B30 of 0.770833333333334. If you don't want to change the formula then you could, instead, eliminate the formulae in the B8:B33 range, e.g. entering the value 0.75 in B29 will also cause E30 to turn red (when the date in F2 is June 1st, 2020).