# Thread: Find the Clash of duty time in excel

1. ## Find the Clash of duty time in excel

Hi,

If i want to create a duty schedule for my employees and i want to check the clash of duty for a person.For example
John has a schedule for Monday 9.30 to 10.30 in Department A.... if i enter the same name for Department B on Monday 9.30 to 10.30
excel should show an error and highlight cells for clash of duty as he cannot do the duty at the same time in two different departments.

3. Hi,

If i want to create a duty schedule for my employees and i want to check the clash of duty for a person.For example
John has a schedule for Monday 9.30 to 10.30 in Department A.... if i enter the same name for Department B on Monday 9.30 to 10.30
excel should show an error and highlight cells for clash of duty as he cannot do the duty at the same time in two different departments.
In the sheet i have used a color to highlight but how to do that with the help of a formula

4. This won't work. You need to have a column for start time and another for finish time populated with proper times, not text. Then you can put together an easy formula.

5. Hope this sheet is okay..Please find the attached file

6. Originally Posted by vinoo
he cannot do the duty at the same time in two different departments.
Code:
```=AND(IF(COUNTIF(\$F\$2:\$F2;\$A2&\$C2)>1;TRUE;FALSE)=TRUE;IF(COUNTIF(\$G\$2:\$G2;\$A2&\$B2)>1;TRUE;FALSE)=FALSE)
=AND(COUNTIFS(\$A\$2:\$A\$7;\$A2;\$C\$2:\$C\$7;\$C2)+COUNTIFS(\$A\$2:\$A\$7;\$C2;\$C\$2:\$C\$7;\$A2)>1;COUNTIFS(\$B\$2:\$B\$7;\$B2;\$C\$2:\$C\$7;\$C2)+COUNTIFS(\$B\$2:\$B\$7;\$C2;\$C\$2:\$C\$7;\$B2)>1)
=AND(COUNTIFS(\$A\$2:\$A\$7;\$A2;\$C\$2:\$C\$7;\$C2)+COUNTIFS(\$A\$2:\$A\$7;\$C2;\$C\$2:\$C\$7;\$A2)>1;COUNTIFS(\$B\$2:\$B\$7;\$B2;\$C\$2:\$C\$7;\$C2)+COUNTIFS(\$B\$2:\$B\$7;\$C2;\$C\$2:\$C\$7;\$B2))```

7. FYI
1. select B7:F30
2.Select "conditional formating"->new rule->Use formula...., then paste following formula to "Format values"

=AND(B7<>"",COUNTIFS(\$B\$7:\$B\$30,B7,\$C\$7:\$C\$30,C7,\$D\$7:\$D\$30,D7,\$E\$7:\$E\$30,E7,\$F\$7:\$F\$30,F7)>1)
3. choose your color for highlight, "ok" to exit

Originally Posted by vinoo
Hi,

If i want to create a duty schedule for my employees and i want to check the clash of duty for a person.For example
John has a schedule for Monday 9.30 to 10.30 in Department A.... if i enter the same name for Department B on Monday 9.30 to 10.30
excel should show an error and highlight cells for clash of duty as he cannot do the duty at the same time in two different departments.
In the sheet i have used a color to highlight but how to do that with the help of a formula