Multiple issues with scheduling. Column check for similar values within a certain cell range.

[SteveNL86]

Dear reader,

I'm running into a few issue's with a schedule I'm working on. What I'm trying to achieve is a warning message in a column if a name repeats itself in a certain range in another column. The idea is basicly that I don't want to schedule people for a shift if they already have a shift planned 3 days before or after the shift I'm trying to fill in.

For example

januari	2020/01/16	do	Shift 1	Jack	STOP
januari	2020/01/16	do	Shift 2	Rose	Ok
januari	2020/01/17	vr	Shift 1	Molly	Ok
januari	2020/01/17	vr	Shift 2	John	Ok
januari	2020/01/18	za	Shift 1	Jack	STOP
januari	2020/01/18	za	Shift 2	Susan	Ok
januari	2020/01/19	zo	Shift 1	Rick	Ok
januari	2020/01/19	zo	Shift 2	Edin	Ok
januari	2020/01/20	ma	Shift 1	Bob	STOP
januari	2020/01/20	ma	Shift 2	Steve	Ok
januari	2020/01/21	di	Shift 1	Elisabeth	Ok
januari	2020/01/21	di	Shift 2	Molly	Ok
januari	2020/01/22	wo	Shift 1	Nick	Ok
januari	2020/01/22	wo	Shift 2	Bob	STOP
januari	2020/01/23	do	Shift 1	Jack	Ok
januari	2020/01/23	do	Shift 2	Rose	Ok
januari	2020/01/24	vr	Shift 1	John	Ok
januari	2020/01/24	vr	Shift 2	Suzy	Ok
januari	2020/01/25	za	Shift 1	Mark	Ok
januari	2020/01/25	za	Shift 2	Rick	Ok

So in this example both Jack and Bob have shifts that are too close to eachother. So I'm trying to get an automatic warning in the last column if this happens.

The next issue is I'm trying to pay special attention to weekend shifts. I dont want to schedule people for back to back weekends. So a similar issue but instead of looking just 3 days before/after I want to check that they dont have a shift planned the weekend before or after a certain weekend.

And lastly I'm trying to avoid giving people the same shift back to back. Let's say I have 5 different shifts, all named differently. If I'm planning a shift I would like a similar warning if the person has that same shift planned before or after the shift I'm planning.

Also it would make it a lot easier if the warning displayed the problem, like "Double Weekend", "Same Shift", "Too Close".

I hope I'm making sense here, any help is much appreciated!

Kind regards,
Steve