Formula to indicate overlap of time formatted cells?
Column C has date (e.g. 9/1/2013)
Column D has start time (e.g. 1:00:00 PM)
Column E has end time (e.g. 3:00:00 PM)
My goal is to figure out where there is overlap of times within the same date. For example:
In cells C2 and C3 is the same date, 9/1/2013.
In cell D2 is 1:00:00 PM. In cell E2 is 3:00:00 PM.
In cell D3 is 2:00:00 PM. In cell E3 is 4:00:00 PM.
So there is an overlap of one hour (between 2:00:00 PM and 3:00:00 PM). Is there a formula I can use to indicate/highlight this?
Re: Formula to indicate overlap of time formatted cells?
Try pasting the following into F2 and copying down.
=IF((COUNTIFS(C:C,C2,E:E, ">" & D2,D:D,"<"& E2)-1)>0,"Overlap","No overlap")
Users Browsing this Thread
There are currently 1 users browsing this thread. (0 members and 1 guests)
By excelarator in forum Excel Formulas & Functions
Last Post: 02-07-2013, 12:41 PM
By olafurbj in forum Excel General
Last Post: 09-16-2011, 12:32 PM
By tcowen61 in forum Excel General
Last Post: 09-04-2011, 05:52 AM
By Chutney in forum Excel General
Last Post: 11-30-2009, 07:52 AM
By William DeLeo in forum Excel General
Last Post: 06-06-2006, 03:26 PM
Search Engine Friendly URLs by vBSEO 3.6.0 RC 1