how to find time clashes using excel

[Jack]

Hi, Excel Stars,

I am puzzled with a task to find the potential time clashes when planning
exams.

After allocating times to diffrent subjects, there will be some of the
students got a timetable as below

Name Subject Date Time
Mary....................................................................
Mary ....................................................................
Mary ....................................................................
Tom English Tuesday 2/10/08 9.00 am to 11.00 am
Tom Chemistry Tuesday 2/10/08 9.00 am to 11.00 am
Tom Math Wendesday 3/10/08 9.00 am to 11.00 am
Tom Chemistry Thrusday 4/10/08 9.00 am to 11.00 am
Jack ........................................................................
Jack ......................................................................
Jack.......................................................................

Out of 700+ students, there will be 5 to 10 students having time clashes (as
above) on their exam timetables.

Is there any way to use excel functions or macros to highlight the time
clashes?

much appriciated for your help!

 

[Stefi]

If your columns are like this
A B C D
Name Subject Date Time

and dates in column C are real Excel dates (not strings) then
enter this formula in E2
=C2+TIMEVALUE(LEFT(SUBSTITUTE(D2,".",":"),8))
this one in F2
=SUMPRODUCT(--($A$1:$A$10=A2),--($E$1:$E$10=E2))>1

and fill them down as necessary! In column F you'll get TRUE in case of
clashes, you can Autofilter them.
Formulae could become simpler if time values would appear in normal form,
e.g. 9:00 instead of 9.00, in this case formula in E2:
=C2+TIMEVALUE(LEFT(D2,8))

Regards,
Stefi

„Jack†ezt írta:

 

[Herbert Seidenberg]

Time schedule overlap
Excel 2007
http://www.mediafire.com/?sharekey=8756bfaf09e707abd2db6fb9a8902bda

 

[Jack]

Hi, Stefi,

Thank you so much! You get rid of a big headache for me. It is working
perfectly. You are a champion!

I would like to know more about the functions of Excel, any suggestions for
books or websites about them?

Much appriciated if you can expand more how to use sumproduct function in
this case. what dose '--' and > 1 do in this function.

 

[Stefi]

SUMPRODUCT in this case is used to count cases with more than one criteria
(COUNTIF can be used with only one criterium).
From this formula:
=SUMPRODUCT(--($A$1:$A$10=A2),--($E$1:$E$10=E2))>1

$A$1:$A$10=A2 returns a 10 element array of TRUEs and FALSEs: You get TRUE
if the nth cell of range $A$1:$A$10 equals the name in A2.
-($A$1:$A$10=A2) : the minus sign is equivalent with multiplying by -1, it
forces Excel to convert logical values to numbers (TRUE->1, FALSE->0),
because of the minus sign TRUE->1 changes to TRUE->-1
The second minus sign converts -1s to +1s

Column E contains Excel date/time values of exams's start dates,
array returned by --($E$1:$E$10=E2) contains 1s if the nth cell of range
$E$1:$E$10 equals the start time in E2.

Multiplying the elements of the two arrays gives 1 if both criteria is met
(1*1), 0 otherwise (1*0, 0*1, 0*0).

If this amount is greater than 1 then there are more then one rows with the
same name and same start time in the range.

I hope my explanation is clear enough.

I think that Excel Help and forums like this one can give satisfactory
information - at least at a base level - on Excel functions and other
facilities. I never used books for studying Excel. Reading posts in this
forum you'll find references to several useful web pages maintained by Excel
gurus, e.g. http://www.cpearson.com/Excel
Google can also be used for finding pages dealing with a certain topic.

Regards,
Stefi

 

[Jack]

Hi, Stefi,

Much appriciated for your detailed explaination. It is very clear and I
learnt a lot from it. Merry Christmas and Happy New Year!

 

[Stefi]

You are welcome! Thanks for the feedback! Best wishes!
Stefi

„Jack†ezt írta:

 

[anredremix]

Hi
I have a some what related doubt.but couldnt figure it out.

I need to put a red flag if any two rwos that conflict because of meeting place,day or time.
eg:1st and 2nd row have conflict.

room# day time
room1 M 1:00-2:00
room1 M 1:00-2:00
room2 T 1:00-2:00
room3 W 1:00-2:00