robgoldstein
Board Regular
- Joined
- Oct 26, 2013
- Messages
- 165
- Office Version
- 2019
- Platform
- Windows
Hey All,
I am building a schuduling workbook that has a sheet that is populated by by form with my employees availablities by day for a week. Each row is an employee's availability and has the date of the monday of that week at the beginning. The columns of this availablity sheet are by day and contain the hours they are not available (ie 10:00, 11:00, 12:00) for that day or it says "all day"
I have another I will be using to schedule for that week. It has the date of the Monday of that week (which will match the one from the employees availability) in cell B1 The rows will have the employee's name and email address (also in their form caputred info) and the columns will be hours scheduled , grouped by days.
I want to be able to fill in the cells with RED where the employee says they are not available. So I would need to match the date, email and time for each day. I deally this would a single formula that will all the way down and across the schedule in conditional formatting.
I hope this clear.
Here are the 2 sheets. Any help is appreciated.
Schedule sheet
Form responses sheet
EDIT: So for the row containing Robgoldstein@gmail.com on the schedule sheet for the week starting on 2021-08-03 on Monday I would want Monday all red, Tuesday I would need 10, 11, 12, 1 & 2 to all be filled in Red and Wednesday I would not need anything filled in Red.
I am building a schuduling workbook that has a sheet that is populated by by form with my employees availablities by day for a week. Each row is an employee's availability and has the date of the monday of that week at the beginning. The columns of this availablity sheet are by day and contain the hours they are not available (ie 10:00, 11:00, 12:00) for that day or it says "all day"
I have another I will be using to schedule for that week. It has the date of the Monday of that week (which will match the one from the employees availability) in cell B1 The rows will have the employee's name and email address (also in their form caputred info) and the columns will be hours scheduled , grouped by days.
I want to be able to fill in the cells with RED where the employee says they are not available. So I would need to match the date, email and time for each day. I deally this would a single formula that will all the way down and across the schedule in conditional formatting.
I hope this clear.
Here are the 2 sheets. Any help is appreciated.
Schedule sheet
| Week Starting on Monday | 2021-08-24 | Week ending on Suday | 2021-08-30 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Monday | Tuesday | Wednesday | Thursday | Friday | Saturday | Sunday | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 2021-08-24 | 2021-08-25 | 2021-08-26 | 2021-08-27 | 2021-08-28 | 2021-08-29 | 2021-08-30 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Name | 10:00 | 11:00 | 12:00 | 01:00 | 02:00 | 03:00 | 04:00 | 05:00 | 06:00 | 07:00 | 08:00 | 10:00 | 11:00 | 12:00 | 01:00 | 02:00 | 03:00 | 04:00 | 05:00 | 06:00 | 07:00 | 08:00 | 10:00 | 11:00 | 12:00 | 01:00 | 02:00 | 03:00 | 04:00 | 05:00 | 06:00 | 07:00 | 08:00 | 10:00 | 11:00 | 12:00 | 01:00 | 02:00 | 03:00 | 04:00 | 05:00 | 06:00 | 07:00 | 08:00 | 10:00 | 11:00 | 12:00 | 01:00 | 02:00 | 03:00 | 04:00 | 05:00 | 06:00 | 07:00 | 08:00 | 10:00 | 11:00 | 12:00 | 01:00 | 02:00 | 03:00 | 04:00 | 05:00 | 06:00 | 07:00 | 08:00 | 10:00 | 11:00 | 12:00 | 01:00 | 02:00 | 03:00 | 04:00 | 05:00 | 06:00 | 07:00 | 08:00 | |
| Employees Scheduled | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| KeyHolders Scheduled | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Key Holdder- Rob | Robgoldstein@gmail.com | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Default | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Key Holder - Mandy | mandy.carruthers@hotmail.com | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Default | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Key Holder - Kasandra | Kassandra_gallant@hotmail.com | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Default | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Form responses sheet
| Fluffy's Scheduling page Requests.xlsx | |||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | |||
| 1 | Timestamp | Name | Email Address | Please select the Monday of the week your are submitting your time off requests. | Select times you are not available on Monday. | Select times you are not available on Tuesday. | Select times you are not available on Wednesday | Select times you are not available on Thursday | Select times you are not available on Friday | Select times you are not available on Saturday | Select times you are not available on Sunday | Notes | |||
| 2 | 8-19-2021 10:25:38 | rob test 1 | robgoldstein@gmail.om | 2021-08-23 | All Day | 10:00 am, 11:00 am, 12:00 pm, 1:00 pm, 2:00 pm | 7:00 pm, 8:00 pm | 3:00 pm, 4:00 pm, 5:00 pm | 10:00 am, 11:00 am, 12:00 pm | this is a note | |||||
| 3 | 8-19-2021 11:18:09 | Mandy test 1 | mandy.carruthers@hotmail.com | 2021-08-23 | 2:00 pm, 3:00 pm | All Day | 10:00 am, 11:00 am, 12:00 pm | more notes | |||||||
| 4 | |||||||||||||||
| 5 | |||||||||||||||
Form responses 1 | |||||||||||||||
EDIT: So for the row containing Robgoldstein@gmail.com on the schedule sheet for the week starting on 2021-08-03 on Monday I would want Monday all red, Tuesday I would need 10, 11, 12, 1 & 2 to all be filled in Red and Wednesday I would not need anything filled in Red.
Last edited by a moderator:
