# How can I improve my 'AND' function for my weekly staff rota based on week number.

#### AndrewJelley

##### New Member
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

#### Attachments

• week formula.PNG
72.5 KB · Views: 22

### Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
I have added to this myself
=IF(AND(ISODD(\$B\$1),\$B7="Team 2"),"14:30","06:00")
that I can apply to just the D column of the rows of people on either Team 1 or Team 2, but then my formula's in the other cells don't work with the newly updated cells??? and just leaves me with blank cells all the way along, instead of the bar of colour.

Excel Formula:
``=IF(B3="","",IF(B3="Office",8,IF(B3="Perm AM",6,IF(B3="Perm PM",14.5,IF(B3="Team 1",IF(ISODD(B\$1),6,14.5),IF(B3="Team 2",IF(ISODD(B\$1),14.5,6),"")))))/24)``

Excel Formula:
``=IF(B3="","",IF(B3="Office",8,IF(B3="Perm AM",6,IF(B3="Perm PM",14.5,IF(B3="Team 1",IF(ISODD(B\$1),6,14.5),IF(B3="Team 2",IF(ISODD(B\$1),14.5,6),"")))))/24)``
Thank you for this, but I've created a permutation table and used it as a reference, then I can update the table if anything changes.

Ok,

Replies
2
Views
200
Replies
0
Views
208
Replies
7
Views
400
Replies
5
Views
99
Replies
3
Views
169

1,206,833
Messages
6,075,130
Members
446,123
Latest member
junkyardforme

### 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.

### Which adblocker are you using?

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

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