CODEWITHSUNDEEP
sqlexcelvba
user2253258
user2253258

Reputation: 11

Detect time range duplication in Excel worksheet

I have a schedule table in excel that contain :

Task - Begine Time - End Time
A    - 10:00       - 10:30
B    - 10:35       - 11:44
C    - 10:20       - 11:15
D    - 12:00       - 13:12

I want to create a formula that detect if a task is scheduled in another task time ; Eg : Task C begin when Task B is running.

Thanks

Upvotes: 0

Views: 84

Answers (3)

Jon G
Jon G

Reputation: 4164

Best way to solve this would be to sort the tasks into order of start time then by finish time, then you can just check for each start time whetehr the finish time in the row above is later than the start time for the current row, then you have an overlap.

 (Col)(A)    (B)           (C)      
(Row) Task - Begin  Time - End Time - Okay?
(1)   A    - 10:00       - 10:30    - 
(2)   C    - 10:20       - 11:15    - =B2 > C1
(3)   B    - 10:35       - 11:44    - =B3 > C2
(4)   D    - 12:00       - 13:12    - =B4 > C3

Upvotes: 1

MikeyB
MikeyB

Reputation: 301

Add another column to your table to check whether your begin times for each task occur before the end of any other tasks AND after the start of those tasks:

Task    Start   End Clash?
a   10:00   10:30   0
b   10:35   11:44   1
c   10:20   11:15   1
d   12:00   13:12   0

The formula I have put in the 'Clash?' column is as follows:

=SUMPRODUCT(N([Start]<[@Start]),N([End]>[@Start]))

Hope that helps!

Upvotes: 0

pnuts
pnuts

Reputation: 59442

From your question I am not sure whether you want SQL, VBA or a formula solution so offer a chart for consideration:

SO26115451 example

Upvotes: 0

Related Questions