excel formula for finding date/time overlap

[NikiDivi]

I'm looking for an Excel formula to return a true or false value for a range of dates with times (mm/dd/yyyy hh:mm AM PM) that overlap.

 

[awoohaw]

Try this:

Mr Excel Questions 73.xlsm
	A	B	C
1
2	DateTime1	12/05/2023 10:30 AM
3	DateTime2	12/05/2023 10:30 PM
4
5	Test DateTime	In Range
6	12/04/2023 01:00 PM	FALSE
7	12/05/2023 05:00 AM	FALSE
8	12/05/2023 10:30 AM	TRUE
9	12/05/2023 05:00 PM	TRUE
10	12/05/2023 11:40 PM	FALSE
11	12/06/2023 05:00 AM	FALSE
NikiDivi

Cell Formulas
Range	Formula
B6:B11	B6	=AND(A6>=$B$2,A6<=$B$3)

 

[NikiDivi]

Thank you for your suggestion. I tried =AND(F2>=$E$2,F2<=$E$2), and they all returned false. I've got more than 600 rows of data. Any other suggestions?

 

[awoohaw]

Your formula has an error:

you have

=AND(F2>=$E$2,F2<=$E$2)

Assuming Date is E, Start is F, Stop is G:
try:

=AND(E2>=F2,E2<=G2)

Also, you are using ABSOLUTE references which will not work for you in your scenario.

 

[NikiDivi]

You might have noticed that I'm a novice but Date is D, START is E, STOP is F, Overlapping is G. Do you have any suggestions that might work?

 

[awoohaw]

I didn't say this earlier, but make sure your dates are properly formatted.
As well as making sure they are indeed date values.
1. Select Columns D, E, F, and change format to numbers with some decimals - the number of decimal places is not that important, but choose 5
2. The numbers in the cells should all be right aligned and have 5 decimals.
3. If a column still looks like a date, it is not a date - it is text, and you need to fix your data. (search for how to convert dates as text into dates).
4. If the cells all look like numbers (right aligned and 5 decimal spaces), then change the format to a consistent date format.

Once that is done, then this:

If cell G1 is "Overlapping" then the first formula is in cell G2.
Type this is cell G2:

=AND(D2>=E2,D2<=F2)

copy down.

 

[Rick Rothstein]

Your dates in Column D are not in the same month as those in Columns E and F. For example, your date in cell D2 is 2/1/2023 and you are checking it against (omitting the leading zeros) 1/2/2023 dates. Second, your start and end dates are both the same day with a 15-minute span, but the dates in Column D have no time value associated with them, so by default, they are assumed to be midnight. That means even if you straighten out the month differences, you will never get a TRUE because you have no time value to check.

 

[awoohaw]

Your dates in Column D are not in the same month as those in Columns E and F. For example, your date in cell D2 is 2/1/2023 and you are checking it against (omitting the leading zeros) 1/2/2023 dates. Second, your start and end dates are both the same day with a 15-minute span, but the dates in Column D have no time value associated with them, so by default, they are assumed to be midnight. That means even if you straighten out the month differences, you will never get a TRUE because you have no time value to check.

Rick, great points about the time intervals. But, he does mention that he has 600 dates to review, maybe there are some include dates. I also think the first column could be a text value instead of a date value because of the left justification.

@NikiDivi If you have some examples of expected TRUES please share those. Also, I suggest using the xl2bb add in (link below) to share your mini worksheet.

 

[Rick Rothstein]

Rick, great points about the time intervals. But, he does mention that he has 600 dates to review, maybe there are some include dates. I also think the first column could be a text value instead of a date value because of the left justification.

True, but the impression I got from his post was that he expected the ones he showed us to be TRUE. But I could be wrong, of course.