Update excel value based on day of the week

[blaqoutent23]

Hi,

I'm creating a spreadsheet with a list of all my employees, however rather than updating their availability daily, is there a code so that their shift is updated depending on the business day within every 2 weeks?

Ex: Employee A is off on the 1st and 2nd Wednesday every 2 weeks, so when it's Wednesday their shift will automatically update.

 

[sijpie]

I have something like this. Next to the column with names I have a column where i enter the days of the week people don't work (1 for sunday, 7 for saturday for example). Then in the schedule in the top row I have the dates, below that in a row (which i later hide) i have the formula that calculates the day of the week from the date above.

I then have conditional formatting that checks the day-off numbers against the day of the week and sets the colours