Find the Clash of duty time in excel

V

vinoo

New member
Joined
Jun 30, 2019
Messages
3
Reaction score
0
Points
0
Excel Version(s)
2007
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
 
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

 

Attachments

  • Clash of duty time.xlsx
    9.5 KB · Views: 42
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.
 
Hope this sheet is okay..Please find the attached file
 

Attachments

  • Clash of duty time.xlsx
    9.5 KB · Views: 39
vinoo said:
he cannot do the duty at the same time in two different departments.
Click to expand...
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))
 

Attachments

  • vinoo-navic10086.xlsx
    35.4 KB · Views: 44
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


vinoo said:
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
Click to expand...
 
You must log in or register to reply here.
Back
Top