+ Reply to Thread
Results 1 to 2 of 2

Formula to indicate overlap of time formatted cells?

  1. #1
    Registered User
    Join Date
    08-04-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    17

    Question 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?

  2. #2
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,967

    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")
    Martin

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] IF Statements Involving Time Formatted Cells
    By excelarator in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-07-2013, 12:41 PM
  2. How to totalise time formatted cells
    By olafurbj in forum Excel General
    Replies: 13
    Last Post: 09-16-2011, 12:32 PM
  3. Nested If Statement using Time formatted cells
    By tcowen61 in forum Excel General
    Replies: 5
    Last Post: 09-04-2011, 05:52 AM
  4. Add values to time formatted cells
    By Chutney in forum Excel General
    Replies: 1
    Last Post: 11-30-2009, 07:52 AM
  5. formula to determine time range overlap?
    By William DeLeo in forum Excel General
    Replies: 0
    Last Post: 06-06-2006, 03:26 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1