MEUserII
Board Regular
 Joined
 Oct 27, 2017
 Messages
 81
 Office Version

 365
 2021
 2019
 2016
 2013
 Platform

 Windows
Consider time interval: 08:00AM to 09:30AM which is to the constant time interval against which a dynamic list of other time intervals such as: 06:00AM to 07:30AM, 7:30AM to 09:00AM, 09:00AM to 10:30AM, 10:30AM to 12:00PM, and 07:30AM to 10:30AM; are to be checked against to see if any of the dynamic list of other time intervals overlaps with the constant time interval: 08:00AM to 09:30AM  how would you do this by formula, say by a COUNTIFS formula?
For reference, I attempted to do the following:
If for each of the dynamic list of time intervals, they are separated in to their beginning times and end times with: column A for beginning times and column B for end times; then you would have the following:
A2:A6 = 06:00AM, 07:30AM, 09:00AM, 10:30AM, 07:30AM
B2:B6 = 07:30AM, 09:00AM, 10:30AM, 12:00PM, 10:30AM
Using column C to convert A2:A6 to Excel timevalues and using column D to convert B2:B6 to Excel timevalues the following two formulas are entered in to C2/D2 and copied down to C6/D6.
C2 (copied down to C6) = TIMEVALUE((LEFT( ($A2), (5) ) )&(" ")&(RIGHT( ($A2), (2) ) ) )
D2 (copied down to D6) = TIMEVALUE((LEFT( ($B2), (5) ) )&(" ")&(RIGHT( ($B2), (2) ) ) )
So, my overall attempt with the two helper columns of C2:C6 and D2:D6 becomes this formula entered in E2:
=COUNTIFS( ($C$2:$C$6), ((">=")&(TIMEVALUE((LEFT( ("08:00AM"), (5) ) )&(" ")&(RIGHT( ("08:00AM"), (2) ) ) ) ) ), ($D$2:$D$6), (("<")&(TIMEVALUE((LEFT( ("09:30AM"), (5) ) )&(" ")&(RIGHT( ("09:30AM"), (2) ) ) ) ) ) )
So, that if the COUNTIFS formula above equals zero (=0); I would say "no overlap" and if the COUNTIFS formula above does not equal zero; I would say "yes overlaps".
However, this COUNTIFS formula does not seem to be right as the time interval: 07:30AM  10:30AM is NOT counted, BUT still overlaps in the constant time interval: 08:30AM  09:30AM.
So, how would I write a formula to check/count if the dynamic time intervals listed overlap with the constant time interval listed?
For reference, I attempted to do the following:
If for each of the dynamic list of time intervals, they are separated in to their beginning times and end times with: column A for beginning times and column B for end times; then you would have the following:
A2:A6 = 06:00AM, 07:30AM, 09:00AM, 10:30AM, 07:30AM
B2:B6 = 07:30AM, 09:00AM, 10:30AM, 12:00PM, 10:30AM
Using column C to convert A2:A6 to Excel timevalues and using column D to convert B2:B6 to Excel timevalues the following two formulas are entered in to C2/D2 and copied down to C6/D6.
C2 (copied down to C6) = TIMEVALUE((LEFT( ($A2), (5) ) )&(" ")&(RIGHT( ($A2), (2) ) ) )
D2 (copied down to D6) = TIMEVALUE((LEFT( ($B2), (5) ) )&(" ")&(RIGHT( ($B2), (2) ) ) )
So, my overall attempt with the two helper columns of C2:C6 and D2:D6 becomes this formula entered in E2:
=COUNTIFS( ($C$2:$C$6), ((">=")&(TIMEVALUE((LEFT( ("08:00AM"), (5) ) )&(" ")&(RIGHT( ("08:00AM"), (2) ) ) ) ) ), ($D$2:$D$6), (("<")&(TIMEVALUE((LEFT( ("09:30AM"), (5) ) )&(" ")&(RIGHT( ("09:30AM"), (2) ) ) ) ) ) )
So, that if the COUNTIFS formula above equals zero (=0); I would say "no overlap" and if the COUNTIFS formula above does not equal zero; I would say "yes overlaps".
However, this COUNTIFS formula does not seem to be right as the time interval: 07:30AM  10:30AM is NOT counted, BUT still overlaps in the constant time interval: 08:30AM  09:30AM.
So, how would I write a formula to check/count if the dynamic time intervals listed overlap with the constant time interval listed?
Last edited: