Highlight the Days of the Week With This Tutorial.

Posted on January 16, 2008 
Filed Under Excel Tips & Tricks, Reader Questions

I wrote yesterday about a formula to automatically display the weekends in a column. Pretty soon after I published the article I received an e-mail with a very good question from a guy from my birth city Rotterdam named Michel. So what else could I have done than to fit this reader question right in;).

 

Michel asked me this; this is a very good tip but now you’ve entered the weekends in a second column, what if I don’t want to add a second column because that is where my data is filled in. Is there a way to display the weekends in another way?

 

Yes, there is; it’s possible to change the fonts, the background colour or have the dates underlined or bolded.

 

In this Excel Tip and Trick I’m showing you how to highlight the weekends by giving the cells another background colour.

 

I’m using the same data as in yesterday’s article.

 

  • Select the Cells A2:A10
  • Go to Conditional Formatting
  • And select New Rule

 

Excel-tip-trick-pic1

 

  • Now select “Use a formula to determine which cells to format”
  • And type in =WEEKDAY($A2)=1
  • Now select Format
  • Go to the Fill tab and select a colour.
  • And click OK and at the OK at the New Formatting Rule Window.

 

Excel-tip-trick-pic2 

 

 

 

 

 

 

 

 

 

 

 

You have now marked the Sundays because this function returns the days as an integer ranging from 1 (Sunday) to 7 (Saturday) by default.

 

Excel-tip-trick-pic3 

 If you want to mark the Saturdays with an other colour you change the 1 into a 7 which is now the formula  =WEEKDAY($A2)=7.

  

Excel-tip-trick-pic4 

 

 

 

 

 

 

Excel-tip-trick-pic5

  

 

 

 

  

I hope you all liked this Excel Tip & Trick and thanks Michel for sending it in.

Do you want to see more? This is related...


Buzz Up Digg Mixx
Print


Comments

One Response to “Highlight the Days of the Week With This Tutorial.”

  1. Dragos on March 12th, 2009 4:03 pm

    Hi,

    I put the month days in columns instead of rows and I want to apply the same conditional formatting but I really have a hard time doing this. Could you kindly explain how do I have to do it?

    Thanks.

Leave a Reply