AndrewJelley
New Member
- Joined
- Nov 21, 2021
- Messages
- 5
- Office Version
- 365
- Platform
- Windows
So I have created a rota in excel that uses conditional formatting to give me a visual display of who is in at what times.
=--AND($D3<=G$2,$E3>G$2)
So if I make a change in the D or E columns for the start time or finish time, the cells will display green for if they are in at that time, and blank if they are not in. I have this bit all complete.
My problem is now that I have to still type in the start and finish times manually. As you can see, I have in the screenshot; Team 1, Team 2, Office Hours, Perm AM and Perm PM. You can also see this worksheet is Week 49. I want to create a formula that I can put down the D column, that on an odd week, like week 49, will give those with 'Team 2' the start time of 14:30, and those on 'Team 1' the start time of 06:00, whilst leaving those on 'Office hours', 'Perm AM' and 'Perm PM' unaffected, and will also give each team the opposite hours if the week is even, such as Week 50. I will then adapt this formula for the E column to give the start times.
So far, I have this...
=AND(ISODD(B1),$B7="Team 2")
which returns me a value of true based on the week number being odd, and the Team being Team 2, how can I develop this further to give me the desired results?
If it's not possible to leave the 'Office hours', 'Perm AM' and 'Perm PM' unaffected, then I don't mind just applying the formula to the cells of those on the shift patterns, but a formula I can just run down the whole column would be much better if these people were to end up on Team 1 or Team 2.
any help will be greatly appreciated
=--AND($D3<=G$2,$E3>G$2)
So if I make a change in the D or E columns for the start time or finish time, the cells will display green for if they are in at that time, and blank if they are not in. I have this bit all complete.
My problem is now that I have to still type in the start and finish times manually. As you can see, I have in the screenshot; Team 1, Team 2, Office Hours, Perm AM and Perm PM. You can also see this worksheet is Week 49. I want to create a formula that I can put down the D column, that on an odd week, like week 49, will give those with 'Team 2' the start time of 14:30, and those on 'Team 1' the start time of 06:00, whilst leaving those on 'Office hours', 'Perm AM' and 'Perm PM' unaffected, and will also give each team the opposite hours if the week is even, such as Week 50. I will then adapt this formula for the E column to give the start times.
So far, I have this...
=AND(ISODD(B1),$B7="Team 2")
which returns me a value of true based on the week number being odd, and the Team being Team 2, how can I develop this further to give me the desired results?
If it's not possible to leave the 'Office hours', 'Perm AM' and 'Perm PM' unaffected, then I don't mind just applying the formula to the cells of those on the shift patterns, but a formula I can just run down the whole column would be much better if these people were to end up on Team 1 or Team 2.
any help will be greatly appreciated
