Update excel value based on day of the week

blaqoutent23

New Member
Joined
Sep 28, 2019
Messages
2
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.
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

sijpie

Well-known Member
Joined
Nov 1, 2008
Messages
3,734
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
 

Watch MrExcel Video

Forum statistics

Threads
1,130,274
Messages
5,641,239
Members
417,202
Latest member
AndyVBA

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top