Overlapping dates, times and names

[Jazzman]

Can anybody help me?


I have a database with following columns

A = Id

B = Date (i'm using Finnish date format = dd.m.yyyy)

C = Start time (Finnish time format = hh.mm)

D = End time

E = Name

F = Overlap?


Example:


Row 1: 2 1.1.2013 08:00 - 12:00 Bob Overlap!(because same date-name-overlapping time)

Row 2: 1 1.1.2013 11:00 - 15:00 Bob

Row 3: 3 1.1.2013 11:30 - 16:00 Jack

Row 4: 4 2.1.2013 07:00 - 10:00 Tim

Row 5: 5 2.1.2013 08:00 - 11:00 Tim Overlap!


Note! in row 1 & 2 the overlap will be in row 1 (because higher id number), and in row 4 and 5 the overlap is in row 5 (higher Id)


Each date can have 1 to about 30 entries (and the database up to 30000 entries). What I am trying to do here, when planning the jobscheme, is to not put for example Bob to do 2 jobs at the same time = overlapping times (on the same date).


Now I need a formula that can sort that out, I am only doing changes in the "Name" -column.

 

[Faseeh]

Hi Jazzman,


Will the name always be in sequence or they may appear any where in the database?


Regards,

Faseeh

 

[vasim]

Sort our data by ID & use

=IF(COUNTIFS($E$2:E2,E2,$B$2:B2,B2)>1,"Overlap","")