Excel 公式检查日期和时间是否重叠

Excel 公式检查日期和时间是否重叠

我正在制作一个 Excel 电子表格,用于记录每周的课程安排。表格中有 3 列,人们可以按照以下格式输入课程安排:“[天] [开始时间] [结束时间]”

例子:

不。 开始时间 时间结束
1 甲基硫脲 7:00 AM 9:00 AM
2 特遣队 7:00 AM 9:00 AM
3 上午 8:30 上午 9:30

在此示例中,我希望 Excel 突出显示第 1 行和第 3 行,因为它们在周一有时间重叠

我正在寻找一个也可以放入条件格式的公式。

到目前为止,我只能突出显示时间重叠,但尚未考虑日期。我使用了以下 SUMPRODUCT 公式:

=SUMPRODUCT((M15<$N$15:$N$26)*(N15>$M$15:$M$26))>1

其中 M15 为开始时间,N15 为结束时间。N 列范围表示所有结束时间,M 列范围表示所有开始时间

我希望尽可能使用 Excel 公式并避免使用任何 VB 脚本。但如果仅使用公式无法实现,我愿意听取建议。谢谢!

答案1

只需添加条件列到您提供的公式中。

=SUMPRODUCT((C2<$D$2:$D$4)*(D2>=$C$2:$C$4)*(B2=$B$2:$B$4))>1

在此处输入图片描述



更新:

请尝试以下公式:

SUMPRODUCT((D2>=$C$2:$C$6)*(B2=$B$2:$B$6)*ISNUMBER(FIND("M",$B$2:$B$6))*(COUNT(FIND("M",$B$2:$B$6))>1))>0
SUMPRODUCT((D2>=$C$2:$C$6)*(B2=$B$2:$B$6)*ISNUMBER(FIND("T",$B$2:$B$6))*(COUNT(FIND("T",$B$2:$B$6))>1))>0
SUMPRODUCT((D2>=$C$2:$C$6)*(B2=$B$2:$B$6)*ISNUMBER(FIND("W",$B$2:$B$6))*(COUNT(FIND("W",$B$2:$B$6))>1))>0
SUMPRODUCT((D2>=$C$2:$C$6)*(B2=$B$2:$B$6)*ISNUMBER(FIND("H",$B$2:$B$6))*(COUNT(FIND("H",$B$2:$B$6))>1))>0
SUMPRODUCT((D2>=$C$2:$C$6)*(B2=$B$2:$B$6)*ISNUMBER(FIND("F",$B$2:$B$6))*(COUNT(FIND("F",$B$2:$B$6))>1))>0
SUMPRODUCT((D2>=$C$2:$C$6)*(B2=$B$2:$B$6)*ISNUMBER(FIND("S",$B$2:$B$6))*(COUNT(FIND("S",$B$2:$B$6))>1))>0

在此处输入图片描述

相关内容