# Thread: Find the Clash of duty time in excel

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

Register for a FREE account, and/
or Log in to avoid these ads!

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.
Please help how to highlight if there is a clash

2. Please attach your workbook.

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
Please help how to highlight if there is a clash

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.
If it can help you, try one of these formulas
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
Please help how to highlight if there is a clash

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•