Identify overlaps in employees and days

Hi,

I have a list of employees and their department of employment.

I have a 4 week roster (monday to friday) with 5 slots each day.

I want to assign departments to come in a particular day and work on a task eg tax to come in monday slot 1 and legal to come in monday slot 2.

As I assign these departments, I want to figure out which departments clash on the day as some employees work in multiple departments.

For example I have employee all employees that work in tax and legal so I can't get them to work on the same day and need to assign them separately.

If possible, those clashes would appear in red on the desired results table and those that don't clash, appear normal.

I hope this makes sense.

All employee have tax and legal in common. However, cyber, tele, stat, IT don't have common employees. The basic problem is:

If i assign a department to come in on a particular day, then people who overlap in those departments are highlighted in red.

Hope that helps.

Originally Posted by kvsrinivasamurthy

Select B3:F7. Formula for CF is. Set Format ->>Font->>Red

=(COUNTIF(B$3:B$7,"Tax")+COUNTIF(B$3:B$7,"Legal"))>1
For other ranges change the Row numbers.
Eg: For B8:F12 use B$8:B$12 in formula.

Hi,

I noticed that the formula has legal and tax hard coded, however when i try for IT and Legal (Employee 1,2,3) can be on the same day but it returns true.Is it possibe to make it dynamic as it only works for legal and tax.

Hi @JeteMC, Thanks for your help.

I noticed that the formula hard codes Tax and Legal. I may have other job titles that I need the formula to work dynamically.