Forum Discussion
Conditional formatting for lines which 2 staff members is busy in determined date and hours
Hi everyone,
I had a file where were 4 columns "Date", "Start Time", "End time" and "Tutor" and I needed a conditional formatting formula that fills my line or cell in red when a "Tutor" is inserted two or more times in the file matching the same date and hours in another line, like the following image:
@Quadruple_Pawn helped me to get out of it with this formula that works perfectly:
=SUMPRODUCT(($D$2:$D$10=$D2)*($A$2:$A$10=$A2)*($B$2:$B$10<=$C2)*($C$2:$C$10>=$B2))>1
Howewer, now I have to face a new challenge. I have inserted the "Teacher" column and there is the possibility that a"Tutor" is even a "Teacher".
So now I need a conditional formatting that checks if Tutor and Teacher match in the same date and hours, like the following image:
Do you have a solution also for this?
I hope I was clear in the explanation.
Thank you very much in advance.
If your system uses comma as decimal separator, you should use semicolon to separate the arguments of the function.
In Italian:
=O((CONTA.PIÙ.SE($A$2:$A$8;$A2;$B$2:$B$8;"<="&$C2;$C$2:$C$8;">="&$B2;$D$2:$D$8;$D2) + CONTA.PIÙ.SE($A$2:$A$8;$A2;$B$2:$B$8;"<="&$C2;$C$2:$C$8;">="&$B2;$D$2:$D$8;$F2))>1; (CONTA.PIÙ.SE($A$2:$A$8;$A2;$B$2:$B$8;"<="&$C2;$C$2:$C$8;">="&$B2;$F$2:$F$8;$D2) + CONTA.PIÙ.SE($A$2:$A$8;$A2;$B$2:$B$8;"<="&$C2;$C$2:$C$8;">="&$B2;$F$2:$F$8;$F2))>1)
See the attached demo workbook.
4 Replies
For example:
=OR((COUNTIFS($A$2:$A$8,$A2,$B$2:$B$8,"<="&$C2,$C$2:$C$8,">="&$B2,$D$2:$D$8,$D2) + COUNTIFS($A$2:$A$8,$A2,$B$2:$B$8,"<="&$C2,$C$2:$C$8,">="&$B2,$D$2:$D$8,$F2))>1, (COUNTIFS($A$2:$A$8,$A2,$B$2:$B$8,"<="&$C2,$C$2:$C$8,">="&$B2,$F$2:$F$8,$D2) + COUNTIFS($A$2:$A$8,$A2,$B$2:$B$8,"<="&$C2,$C$2:$C$8,">="&$B2,$F$2:$F$8,$F2))>1)
Hi HansVogelaar
Thank you for your answer but I think that this formula has an error because excel show me an error message when I try to execute it.
If your system uses comma as decimal separator, you should use semicolon to separate the arguments of the function.
In Italian:
=O((CONTA.PIÙ.SE($A$2:$A$8;$A2;$B$2:$B$8;"<="&$C2;$C$2:$C$8;">="&$B2;$D$2:$D$8;$D2) + CONTA.PIÙ.SE($A$2:$A$8;$A2;$B$2:$B$8;"<="&$C2;$C$2:$C$8;">="&$B2;$D$2:$D$8;$F2))>1; (CONTA.PIÙ.SE($A$2:$A$8;$A2;$B$2:$B$8;"<="&$C2;$C$2:$C$8;">="&$B2;$F$2:$F$8;$D2) + CONTA.PIÙ.SE($A$2:$A$8;$A2;$B$2:$B$8;"<="&$C2;$C$2:$C$8;">="&$B2;$F$2:$F$8;$F2))>1)
See the attached demo workbook.
