Conditional formatting based on multiple specific days of week

[alysse]

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

 

[jasonb75]

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]

@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]

Assuming that we're using B3 as the date reference based on the screen capture, the formula would be

=B3-WEEKDAY(B3,2)+7