+ Reply to Thread
Results 1 to 3 of 3

Identify the entry which's date & time is intersecting another entries date & time

  1. #1
    Registered User
    Join Date
    12-05-2011
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    11

    Identify the entry which's date & time is intersecting another entries date & time

    Dear Experts,

    We enter some production data - Production qty., Production Start Date & Time, Production Finish Date & Time etc.

    Say there are four entries of a single machine -

    Order start start time Finish Finish Time
    1 01/12/2012 07:00:00 AM 01/12/2012 12:30:00 PM
    2 01/12/2012 12:30:00 PM 01/12/2012 05:30:00 PM
    3 01/12/2012 01:30:00 PM 01/12/2012 06:00:00 PM
    4 01/12/2012 05:30:00 PM 01/12/2012 07:00:00 PM

    We can see that the 3'rd entery is wrong, as the machine was busy from 12:30 to 5:30 for order no. 2 & then from 5:30 to 7:00 for order no. 4.
    Hence the (entry of ) order 3 can not be processed in-between.
    Such errors happens due to wrong data entry as the data entry team has to do hundreds of entries in a day.

    Our query is how we can identify such entries. We have tried some logic (attached excel file) but failed to find complete solution.
    Please suggest if there is any way out.
    Attached Files Attached Files

  2. #2
    Forum Expert Moo the Dog's Avatar
    Join Date
    09-28-2012
    Location
    Wisconsin
    MS-Off Ver
    Office 365 (PC & Mac)
    Posts
    1,845

    Re: Identify the entry which's date & time is intersecting another entries date & time

    Well, you could use Conditional Formatting to highlight the incorrect cells. For instance, if you select cells E8:E13 and apply this formula in CF:

    =AND(E8<H7,ROW()>8)

    It will highlight any cells in column E where the time is earlier than the time at which the last order was completed. The ROW()>8 part tells it to skip row 8, which is the first row of your data - since there is nothing to compare it to.

    - Moo

  3. #3
    Registered User
    Join Date
    12-05-2011
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    11

    Re: Identify the entry which's date & time is intersecting another entries date & time

    Dear Moo,
    Thanks for looking into for my first post in Excel Forum, the solution given by you works fine for the given data.

    But if the data is unsorted, i.e. the start & fin dates are not in Ascending /descending manner then this will not work.
    Even there will chances where the data of two different machines will be in data table (sorry I missed to mentioned in my earlier post).

    So I want some thing like COUNTIFS or the formula which could handle the range data.

    Please help, thanks in advance...

    Check.xlsx

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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