# Conditional formatting based on multiple specific days of week

#### alysse

##### New Member
Hello,

I'm trying to perform the following:
• Have a specific icon show up when a cell's date is before Sunday
• Have a specific icon show up when a cell's date is before the next Sunday
• Include three more icons for the next three consecutive Sunday's. The final Sunday will be the cell's date is after the final Sunday
Please see attached snip-it to see my attempt at getting a conditional format for the cells, but it's not correct in two ways:
• It's based on a 7-day schedule, not the day of the week
• When the formula is in play it only allocates icons for cells under 7 days and cells over 28 days, all the middle icons don't work

#### Attachments

• Capture.PNG
27.2 KB · Views: 5
37 KB · Views: 5

### Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

#### jasonb75

##### Well-known Member
When the formula is in play it only allocates icons for cells under 7 days and cells over 28 days, all the middle icons don't work
The order needs to be reversed, +28 at the top, then +21, +14, +7
It's based on a 7-day schedule, not the day of the week
You wouldn't be able to apply the condition needed with an icon set unless you add another set of cells to convert the dates to the end of week dates, then apply the icons to those.

#### alysse

##### New Member
@jasonb75 - what would the conversion need to be for the date to round to the following Sunday (not the closest)? I can manage to have that column but I don't know how to write that formula.

#### jasonb75

##### Well-known Member
Assuming that we're using B3 as the date reference based on the screen capture, the formula would be
Excel Formula:
``=B3-WEEKDAY(B3,2)+7``

Replies
3
Views
106
Replies
2
Views
135
Replies
7
Views
160
Replies
6
Views
143
Replies
1
Views
100

1,129,382
Messages
5,635,934
Members
416,889
Latest member
dhegs

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