Hi, I have a number of columns that contain random dates (2017 & 2018), is there a way that I conditionally format the columns so that weekend dates that have passed can be highlighted?
Cheers
Hi, I have a number of columns that contain random dates (2017 & 2018), is there a way that I conditionally format the columns so that weekend dates that have passed can be highlighted?
Cheers
Your CF rule will be like this:
=AND(WEEKDAY(A1,2) > 5,A1 < TODAY())
The cell reference needs to be the first cell containing a date in the range, then set the range in the Applies To box.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Brilliant - thank you very much.
Sorry Ali, do you know if there's a way that only weekend dates that have passed are highlighted? Your formula works brilliantly, however it does highlight weekend dates that are in the future??
Cheers
It shouldn't do, as the criteria include for the date to be earlier than today. Please attach a sample workbook for me to troubleshoot.
=AND
Highlight cells meeting both conditions.
(WEEKDAY(A1,2) > 5,
The day must be higher than 5 (i.e. Saturday or Sunday).
A1 < TODAY())
The date must be earlier than today.
Last edited by AliGW; 10-18-2018 at 11:26 AM.
Thanks Ali. Unfortunately it seems that I am unable to add attachments when replying!!
Yes, you are,
Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.
Thanks for your help.
Since you are using a full column reference in the applies to box, change the formula to this:
=AND(WEEKDAY(C1,2) > 5,C1 < TODAY())
Really appreciate your help, however I still can't get it to work!!! Thanks.
It seems to be working for me.
That's brilliant, very much appreciated.
You’re welcome!
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks