Conditional Formatting Help Needed

Mark Wood
Mark Wood used Ask the Experts™
on
i am trying to set up a conditional formatting rule to show paydays in green. i have paydays setup as a named range for the entire year, Tables!F2:F27.

How would i write the rule to do that? i will attach a copy of the spreadsheet on the next comment.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Mark WoodNetwork Administrator

Author

Commented:
here is the spreadsheet
Time-and-Payments.xlsx
byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013

Commented:
Select cells C4:I17 and use a formula criteria like:
=MATCH(--SUBSTITUTE($B$2,","," " & C4 & ","),PayDates,0)

Open in new window

Time-and-Payments.xlsx
Mark WoodNetwork Administrator

Author

Commented:
The only thing is it is turning Feb 3rd green and it is not a paydate
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
Does this work as desired?
Time-and-Payments.xlsx
byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013

Commented:
You made a lot of extra work by choosing to display dates as text. The better way to do it is to display a real date in each cell, then format the date with ddd if you want the day of the week or d if you want just the day number.

I changed all of your date formulas to be real dates instead of text, then modified the Conditional Formatting accordingly.
Time-and-Payments.xlsx
Mechanical Engineer
Most Valuable Expert 2013
Top Expert 2013
Commented:
I just noticed the Setup sheet and the misspelling for "January". I fixed the error in your data validation list and also simplified the hidden formula in cell F3 to a MATCH.
Time-and-Payments.xlsx
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
Mark,

Did the attached file in the accepted solution work for you? Did you try to change the month on setup sheet and did you find everything normal on Month  Sheet?
Mark WoodNetwork Administrator

Author

Commented:
Thank a lot byundt. Now i just need to try and fix the conditional formatting for the holidays. for some reason it all works great except for March, June, and November. On those months it turns all of the white dates to pink.

Thanks again.
Mark WoodNetwork Administrator

Author

Commented:
after doing some more looking on what you sent the paydates are working perfectly but none of the paid holidays are showing up on the calendar.
Mark WoodNetwork Administrator

Author

Commented:
Subodh,

No it did not work. none of the paid holidays would show and neither did the paydays
Mark WoodNetwork Administrator

Author

Commented:
OK i was able to fix the holidays byundt simply by changing you formula for paydates to =MATCH(C4,HolidayDates,0)

Thanks

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial