日历无法准确高亮时间段问题求助

问题现象 #

日历功能出现异常，部分时间段高亮不完整：比如Data工作表中某条会议时间为6:00 PM to 6:30 PM，日历仅高亮对应日期的6:00 PM单元格，未覆盖整个6:00 PM至6:30 PM的时间段。

问题根源 #

原公式依赖LARGE()+MATCH()的组合逻辑，仅会返回满足条件的最大ID对应的会议。当多个时间单元格属于同一个会议的时间段时，这种取唯一值的逻辑会导致只有起始时间单元格能正确匹配，后续时间单元格无法触发高亮。

另外，原条件中tab_calendar_data[End Time] >= $B7的边界判断不合理：如果会议结束于6:30 PM，通常6:30 PM的单元格不应被高亮（会议已结束），应该改为tab_calendar_data[End Time] > $B7，避免结束时间点被错误包含。

修复后的公式 #

替换原有的LARGE+MATCH逻辑，改用XLOOKUP直接匹配满足条件的会议，确保只要当前单元格时间处于会议时间段内就返回会议名称，触发高亮：

=IFERROR(
XLOOKUP(1,
(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") +
    (DAY(C$6)=DAY(tab_calendar_data[Start Date]))*(tab_calendar_data[Cycle]="monthly") +
    (tab_calendar_data[Start Date]=C$6)*(tab_calendar_data[Cycle]="one-time")
),
tab_calendar_data[Meeting / Deadline],
""),
""
)

额外优化点 #

• 时间格式校验：确认Start Time和End Time列是Excel原生时间值，而非文本格式，避免文本比较导致的逻辑错误。
• 月度循环修正：原公式用MOD(C$6-tab_calendar_data[Start Date],28)判断月度循环，不符合实际月份天数差异，改为DAY(C$6)=DAY(tab_calendar_data[Start Date])更准确。
• 周循环可选调整：若需要严格按7天间隔的周循环（而非仅匹配星期几），可将周循环条件改为MOD(C$6-tab_calendar_data[Start Date],7)=0。
• 条件格式范围：检查条件格式的应用范围是否覆盖所有需要高亮的时间单元格，确认$B7（时间行）和C$6（日期列）的相对引用设置正确。

内容的提问来源于stack exchange，提问作者HSHO