Hi, everyone!
This is my problem. My calendar is in columns A to G. I1 is the value where I want my calendar to start and columns I and J include a list of dates and periods. I want Excel to highlight the dates in the calendar if they match the values in the list or if they are found within a period from that list. If the cell in column J has a "—", it is a specific date. If the cell in column J has a date, that means it is a period. For example, in the case of cells I7 and J7, I want Excel to highlight all the dates in the calendar ranging from Sept 10 to Sept 15.
The formula shouldn't be very difficult, but I'm struggling to get it to work and keep it concise (I really wouldn't want a hundred rules in Conditional Formatting). Could you please help me with this? Thanks!
| Book1 | ||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | |||
| 1 | September 2022 | 9/1/2022 | ||||||||||
| 2 | M | T | W | R | F | S | U | |||||
| 3 | 29 | 30 | 31 | 1 | 2 | 3 | 4 | List of dates | ||||
| 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | Start | End | |||
| 5 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 9/2/2022 | — | |||
| 6 | 19 | 20 | 21 | 22 | 23 | 24 | 25 | 9/5/2022 | — | |||
| 7 | 26 | 27 | 28 | 29 | 30 | 1 | 2 | 9/10/2022 | 9/15/2022 | |||
| 8 | 9/21/2022 | 9/23/2022 | ||||||||||
| 9 | 9/25/2022 | — | ||||||||||
| 10 | 9/28/2022 | 9/30/2022 | ||||||||||
Sheet1 | ||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| A1 | A1 | =DATE(YEAR(I1),MONTH(I1),1) |
| A3 | A3 | =EOMONTH(A1,-1)-(WEEKDAY(A1,2)-1)+1 |
| B3:G7 | B3 | =A3+1 |
| A4:A7 | A4 | =G3+1 |
| A8 | A8 | =IF(MONTH(G7+1)<>MONTH(A1),"",G7+1) |
| B8:G8 | B8 | =IF(A8="","",A8+1) |
This is my problem. My calendar is in columns A to G. I1 is the value where I want my calendar to start and columns I and J include a list of dates and periods. I want Excel to highlight the dates in the calendar if they match the values in the list or if they are found within a period from that list. If the cell in column J has a "—", it is a specific date. If the cell in column J has a date, that means it is a period. For example, in the case of cells I7 and J7, I want Excel to highlight all the dates in the calendar ranging from Sept 10 to Sept 15.
The formula shouldn't be very difficult, but I'm struggling to get it to work and keep it concise (I really wouldn't want a hundred rules in Conditional Formatting). Could you please help me with this? Thanks!
