Date + time overlap checking

How can i check date+time overlap?
See attached excel file.

Checking : ID vs NUMBER vs CONTROL NUMBER vsDate vs Time OVERLAP


Thanks,
zRc55

DATE TIME OVERLAPS.xlsx
Revised sample with explantion..

Checking by means of :
Example: ID 1 usually have 2 sets of number (1 or 2) and have 20 or less control numbers. Control number+date+time would not be overlap with another control number.
Example, Scenario 1:
ID 1, NUMBER 1 , Control number 1 Date+Time ON 8/18/2013 9:00, Date+Time OFF 8/18/2013 8:00, this is overlap.
Scenario 2: ID 1, NUMBER 1 , Control number 2 Date+Time ON 8/18/2013 21:00, Date+Time OFF 8/19/2013 03:00
ID 1, NUMBER 1 , Control number 3 Date+Time ON 8/19/2013 2:00, Date+Time OFF 8/9/2013 8:00, this is overlap.

Thanks Piet Bom

No checking for TIME ON vs TIME OFF overlap?
Ex : ON OFF
Same Date 10:23 9:00

Another checking, Can we check also skip date within the ID


Thanks ,
zRc55

Hi,

Need help:

Another scenario for overlap and date skip.
Please help.



Thanks,
Zaldy

Sorry.

Thanks for the advice Admin.

Thanks you so much PietBom.

May we also request a mcaro for this one if possible?
Because sometimes we have more than 50,000 records to check.


Thanks again.

Hi PietBom,

Hope you can still check my request.

Check Row 23 and 26, there is an error, no checking/errors indicated.
See attached file.


Thanks again in Advance.
zRc55

Hi Piet Bom,

Row 23, Invalid date because in Row 22, date and time is overnight, so the date for row 23 must be August 23 because the date+time is ascending.
Row 26, Invalid date because in Row 25, date and time is overnight, so the date for row 26 must be August 24 because the date+time is ascending.


Thanks,
zRc55

Hi NewDoverman,

I would like to check if the date+time is overlap with the next Control Number with the same ID and also that check for skip date.

8/18/2012 14:20 8/18/2012 18:17 FALSE FALSE
8/18/2012 20:30 8/18/2012 3:17 FALSE FALSE
8/18/2012 3:20 8/19/2012 7:35 FALSE FALSE - with this case, Date for 3:20 is must be 8/19/2012.
8/19/2012 8:15 8/19/2012 13:30 FALSE FALSE

Thanks,
zRc55

Hi PietBom,


Row 23 and 26 is invalid because the date previous to his current date is ascending but the date at row 23 is not ascending.

Explanation :
The date and time for row 2 to 39 must be in ascending order or no overlap of each row and his next row. For every row, the TIME ON and TIME OFF must not be overlap and the next Row must not be also overlap with the previous Rows.



Thanks,
zRc55

Hi PietBom,

Any help please.


Thanks,
Zaldy

Hi PietBom,

Sorry and Thank you so much.

One more thing.
Can we check also if there are missing date in between the START and END of each ID.
See attached file fro the explanation.

If start date is Sept 8 and end to 15. And we find out that there is missing date between Sept 8 to 15 then "error" will show.
Is this possible?



Regards,
Zaldy