Highlight Weekend Dates In Excel

Highlight Weekend Dates in Excel

Yes, the weekend is over, but another one is just five days away! To make it easier to keep track of Saturdays and Sundays, you can use conditional formatting to highlight weekend dates in Excel.


In this example, the Excel file has a list of product sales for the first half of July. We’d like to highlight the dates and sales numbers that fall on a weekend.

Add Conditional Formatting

To highlight the weekend sales:

    1. Select all the cells where conditional formatting should be applied, cells H2:J16 in this example. Cell H2 is the active cell in the selection.

ProdSalesSel

    1. On the Ribbon, click the Home tab, then click Conditional Formatting.
    2. Click New Rule, to open the New Formatting Rule dialog box
    3. In the Select a Rule Type list, click Use a formula to determine which cells to format.
    4. In the Formula box, enter a formula to check the weekday of the date in row 2, which is the active row on the worksheet:
      =WEEKDAY($H2,2)>5

CondFormatWeekend

  1. Click the Format button, and select a Fill colour, or other formatting options, then click OK.
  2. Click OK to close the New Formatting Rule dialog box.

The weekend rows are highlighted in green.

CondFormatGreen

The WEEKDAY Function

The WEEKDAY function checks the date in column H, and returns a weekday number, based on a numbering system (2) that starts with Monday as 1. Saturday is 6 and Sunday is 7, so we want to highlight weekday numbers that are greater than 5.

Instead of that numbering system, we could have entered 1 or 3 as the second argument (return_type) in the WEEKDAY function.

If we entered a 1, or omitted the second argument, the numbering would start with Sunday as 1. With a 3, Monday would start as 0, and end with a 6 on Sunday.

WEEKDAYArg

I used the 2 because that groups Saturday and Sunday at the end of the numbering, and we can simply test for >5.

More About Conditional Formatting

On the Contextures website there are basic instructions for conditional formatting, and other examples of ways you can use it.

Watch the Video

Here’s a short video that shows the steps in Excel 2007.

More Excel Date Tips

Fix Excel dates that won’t change Format

AutoFill Excel dates in series or same Date

How to prevent grouped dates in Excel

Monitor expiry dates in Excel

Excel filter for date range 2 Ways

Count items in a date range in Excel

__________________

29 thoughts on “Highlight Weekend Dates In Excel”

  1. […] Highlight Weekend Dates in Excel – To make it easier to keep track of weekends in Excel, you can use conditional formatting to highlight the rows where the date is a Saturday or Sunday. […]

  2. in the Middle east – weekends are Friday and Saturday – how do i adjust this to mark-up weekends as per Islamic Calendar?

  3. @Sissy,

    In that case, use a 1 in place of the 2 that Deb used in the 2nd argument to the WEEKDAY function or simply omit the 2nd argument altogether as 1 is the default value. Doing this will make the WEEKDAY function return 6 for Friday’s and 7 for Saturday’s. This formula should work for you…

    =WEEKDAY($H2)>5

  4. I have a template that I downloaded online from MSO website. It automatically highlights the weekend days/columns. Further to that, based on start, no of days, and end dates (using a WORKDAY formula) the cells are then formatted with a colour on the gantt, therefore it excludes weekends, however the weekend cells are still formatted with another colour. What formula will tell it not to format the weekend cells at all, but keep them highlighte?

  5. As you have shown the example it looks for dates in a column H.
    But I have a dates in a Row 2. So i want to highlight cells in a column. how would i do that???

  6. I tried the formula to ignore blank cells but I’m not getting it to work. Also when the saturday and sunday are highlighted I would like to record in a column a day each. When we work on weekends we get a compensatory day for each day.

  7. Hi how do i identify weekends if the return_type is three(3)thus monday = 0…….should the function be =Weekday($A1,3)>4

    1. it depends on the Return_type
      following Number returned option are available in excel
      Choose as you need and you can highlight any day as you want no restricted to weekday
      1 or omitted Numbers 1 (Sunday) through 7 (Saturday). Behaves like previous versions of Microsoft Excel.
      2 Numbers 1 (Monday) through 7 (Sunday).
      3 Numbers 0 (Monday) through 6 (Sunday).
      11 Numbers 1 (Monday) through 7 (Sunday).
      12 Numbers 1 (Tuesday) through 7 (Monday).
      13 Numbers 1 (Wednesday) through 7 (Tuesday).
      14 Numbers 1 (Thursday) through 7 (Wednesday).
      15 Numbers 1 (Friday) through 7 (Thursday).
      16 Numbers 1 (Saturday) through 7 (Friday).
      17 Numbers 1 (Sunday) through 7 (Saturday).

  8. Hello,
    how is is possible to colore 1 week end /3 since 14 march 2015 please ?
    i found this but it make a mistake:
    =AND(WEEKDAY($A5,2)>5,MOD((INT(MOD(INT(($A5-2)/7)+3/5,52+5/28))+1)/3,1)=0)
    Excel 2007AB2sam/14/03/2015FAUXFeuil1Worksheet FormulasCellFormulaB2=AND(WEEKDAY($A2,2)>5,MOD((INT(MOD(INT(($A2-2)/7)+3/5,52+5/28))+1)/3,1)=0)

    1. it depends on the Return_type
      following Number returned option are available in excel
      Choose as you need and you can highlight any day as you want no restricted to weekday
      1 or omitted Numbers 1 (Sunday) through 7 (Saturday). Behaves like previous versions of Microsoft Excel.
      2 Numbers 1 (Monday) through 7 (Sunday).
      3 Numbers 0 (Monday) through 6 (Sunday).
      11 Numbers 1 (Monday) through 7 (Sunday).
      12 Numbers 1 (Tuesday) through 7 (Monday).
      13 Numbers 1 (Wednesday) through 7 (Tuesday).
      14 Numbers 1 (Thursday) through 7 (Wednesday).
      15 Numbers 1 (Friday) through 7 (Thursday).
      16 Numbers 1 (Saturday) through 7 (Friday).
      17 Numbers 1 (Sunday) through 7 (Saturday).

  9. I tried the Add Conditional Formatting to highlight just the weekends and it id not work. the entire column was highlighted.

  10. Just in case dates are in columns than rows and you want to highlight weekends and its corresponding rows
    =WEEKDAY(H$2,2)>5 – Observe the “$” placed in Column position
    Task/Date 1-Oct, Sun 2-Oct, Mon 3-Oct, Tue ….
    Task 1 12 8 10
    Task 2 12 8 10
    Task 3 12 8 10
    here the Oct-1, Sun row will be colored i grey out Sat/Sun
    changing criteria you can get what ever day to be highlighted as required

  11. My dates are in a row at the top of the sheet on line L2:AP2, I can get the formula to highlight the weekends on row two but I can’t get the column to highlight. Any suggestions?

  12. Hello
    I have once quick question for you , Please help me out because I’m stuck here.
    Question : For ex : I have a one date as a reference and from this reference date i need to get 3 different dates i.e ( 7 days before the ref date, 2 days before the ref date & 1 day before the ref date) but the twist is that the new date should fall on Weekday and not on Weekend( i.e Sat & sun).
    Please help me out , i’m trying to figure out this from many days.
    Thank you in Advance!!!!

  13. Hi! I’ve been tweaking the formula, but I can’t seem to get it. 🙁 I know =WEEKDAY(H$2,2)>5 highlights Saturday and Sunday. But what if I want to highlight Sunday only?

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.