Hi.
I've been trying to create a formula that checks if the time of two rows overlap and then post true or false. I've had no luck so I've come here for help, what I'm trying to do is have a formula that works like this:
Column J
07.00-12.00
07.00-16.00
So the upper part would check if it overlaps with the lower one and if it does it would be false. I've provided an excel with full of time values, hope my question is clear.
b2=IF(ROWS(B$2:B2)=1,"Not Overlap",IF(AND(LEFT(A2,5)+0>=LEFT(A1,5)+0,LEFT(A2,5)+0<RIGHT(A1,5)+0),"Overlap","Not Overlap"))
try this one and copy towards down
Samba
Say thanks to those who have helped you by clicking Add Reputation star.
Thank you, works great
Thanks for your feedback
Is it possible to have it check dates as well, I realized that alot of times overlap but they're on different dates which is fine, but if they overlap on the same date I'd like to be able to find that.
c2=IF(ROWS(C$2:C2)=1,"Not Overlap",IF(AND(LEFT(B2,5)+A2>=LEFT(B1,5)+A1,LEFT(B2,5)+A2<RIGHT(B1,5)+A1),"Overlap","Not Overlap"))
Try this and copy towards down
Thanks again
You are welcome
I'm still having some issues, I believe it's mainly due to my own editing but I've marked it red in the excel file. It's the time overlapping when it shouldn't. Did I break something?
Q7218=Try the above code in Q7218 and copy towards down
Please Login or Register to view this content.
I see that the rows I used were incorrect, thank you
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks