If I have the days (day, date/month/year) of the year listed in column B (e.g. each day for all of 2014-2015).
What code (or codes) could I use to have the cells that contains the 1st Sunday before the 10th of the month (each month in 2014-2015) to change color. And how do I do it in conditional formatting?
Any help would be greatly appreciated.
/Thomas
I know there are shorter formulas but, assuming your data starts in row 2
=AND(WEEKDAY(B2,1)=1,DAY(B2+7)>=10,DAY(B2)-7<3)
If you want the 10th to be highlighted when it's on a Sunday (instead of the 3rd)
=AND(WEEKDAY(B2,1)=1,DAY(B2+7)>10,DAY(B2)-7<4)
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
It worked, but for one little thing: Saturday is highlighted instead of Sunday. Sorry I did not mention, that my data starts in row 4.
Even if the data starts in row 2, Saturday is still highlighted.
How do I highlight Sunday instead?
Thank you so much, ChemistB.
Last edited by TSKN; 09-26-2014 at 05:45 PM.
Sounds like you have applied the formatting from row 1
With conditional formatting you need to ensure that the formula refers to the first row of the "applies to" range, so if your data starts from row 4, use an "applies to" range that starts at row 4 and change the formula so that it applies to row 4 too.
Be careful changing these, changing one sometimes automatically changes the other so best to delet what you have and start again
Audere est facere
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks