Hello, I'm looking for a formula to color cells based on weekends and holidays. In cells L2:AP2 are the days of the month. Using the conditional formatting rules I used the following formula "=WEEKDAY(L$2:AP$2,2)>5" to determine the cells to be formatted. In addition to weekends I would like to have holidays formatted too. I have a (holiday) named range in cells B991:B1080. I would assume this range could be combined with the current formula I just can't figured it out. Thanks for any assistance someone may provide.
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Conditional Formatting to identify Weekends and Holidays
jakeman
Active Member
- Joined
- Apr 29, 2008
- Messages
- 325
- Office Version
- 365
- Platform
- Windows
Hey there - if your conditional formatting isn't working, try this when determining whether the date in your range falls on a weekend.
Plug this formula into your CF: "Format values where the formula is true:"
=WEEKDAY(L2,2)>5
Make sure that the under the Applies to area, you reference the range in absolute values: =$L$2:$AP$2
You'll then need to create a second CF Rule that evaluates whether one of the dates in your range matches a date value found in your holiday range: =MATCH(L2,Sheet2!$B$991:$B$1080,0). Again, this applies to: =$L$2:$AP$2
Plug this formula into your CF: "Format values where the formula is true:"
=WEEKDAY(L2,2)>5
Make sure that the under the Applies to area, you reference the range in absolute values: =$L$2:$AP$2
You'll then need to create a second CF Rule that evaluates whether one of the dates in your range matches a date value found in your holiday range: =MATCH(L2,Sheet2!$B$991:$B$1080,0). Again, this applies to: =$L$2:$AP$2
Upvote
0
Similar threads
- Question
