Conditional formatting based on multiple specific days of week

alysse

New Member
Joined
Apr 14, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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
    Capture.PNG
    27.2 KB · Views: 5
  • spreadsheet sample.PNG
    spreadsheet sample.PNG
    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
Joined
Dec 30, 2008
Messages
12,601
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Apr 14, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
@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
Joined
Dec 30, 2008
Messages
12,601
Office Version
  1. 365
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top