Automated Roster Hours/Conflict Highlighting.

Hello,

So long time lurker of this forum for finding solutions, first time actually posting!

So I have a roster which looks like this
HeoIOEK.png

In it I have days and activities on the right, for each activity the first 4 characters are the time it starts, in the column to the right it has the length of the activity, sometimes reflected in the title.
What Ive done so far is get some formulas working to count the amount of ocurrences of a particular name in the roster for a particlar day, which looks like this

NaeLxrX.png

I want to convert this to take into account the length of activities it is counting so I can see hours worked each day for everyone. At the moment I am not concerned about All As I will just add it as an extra person.

The other thing I want to do, which is proably a bit more difficult, is to highlight in red any "Conflicts" or whenever a Name appears as having two activities at once.

9mXRLbP.png


As you can see, I have manually highlighted these conflicts here as AA is doing maintenance from 8-8:40 and mail trip from 8:30 and similarly on Saturday.
However I would like this to be automated, so it would conditionaly format any conflicts.

I have attached this workbook at the bottom.


Let me know if you need any more information from me!

Thanks so much for anyone who can help me out!

Hello the 133448 and Welcome to Excel Forum.
I suspect that one reason that you aren't receiving any replies because your file is not accessible, at least I can't get to it.
Please upload the file directly to this site by clicking on the GO ADVANCED button below the Quick Reply window and then scrolling down to Manage Attachments to open the upload window.
Let us know if you have any questions.

It appears that the conflicts occur in rows 8:12 which I numbered 1:5 in column A. With that in mind I set up a table in rows 100:106 that identifies the person that has the conflict using:

Formula:

Please Login or Register  to view this content.

Because of the special case in row 9 where there are pairs another table in rows 108:114 is populated using: =IF(D100="","",MATCH("*"&D100&"*",D$9:D$12,0)+1)
The formula for the conditional formatting rule is: =OR(COUNTIFS(D$108:D$114,ROW(1:1)),COUNTIFS(D$100:D$106,D8)>0)
Let us know if you have any questions.