Weekends or weekdays in Excel

A basic job with Excel is to easily isolate the weekends and the weekdays. This is the case when you hace to build an automatic calendar for example. 

The function WEEKDAY

Presentation

This function returns a value between 1 and 7 corresponding to the value of the day in a week.

=WEEKDAY(Date,Parameter)

This function is very, very, VERY useful to create a test in a IF function or for a conditional formatting.

Explanation of the second parameter

The second parameter is optional but very useful if you are not in USA or Canada.

In USA, the first day of the week is Sunday. So, if you don't specify this parameter 1 = Sunday.

But for most countries in the world, the first day of the week is Monday. This is why it is important to indicate the value of this parameter in function of your localization.

Basic value for the second parameter

For all the versions of Excel, the value for the second parameter could be

  • 1 - The week starts a Sunday (value 1) and finish a Monday ( value 7). It's the default value (or empty)
  • 2 - The week starts a Monday (1) and finish a Sunday (7)
  • 3 - Week starts a Sunday (0) and finish a Monday (6)

Look at this picture to see the different results return for the same date (01/01/2018) in function of second parameter.

Upgrade of the value in the new versions of Excel

For the version of Excel 2010 and more, new values have been added but it's not really useful  except for some Arabic countries where the weekend is not Saturday and Sunday.

  • 11 - The week starts a Monday (1) and finish a Sunday (7)
  • 12 - The week starts a Tuesday (1) and finish a Monday (7)
  • 13 - Week starts a Wednesday (1) and finish a Tuesday (7)
  • ....

How to test if a date is a weekend ?

Now, if you want to know if a date is a weekend or not, you write this simple test with the WEEKDAY function.

=WEEKDAY(Date,2)>5

Or, if you don't want to specify the second parameter, you have to write your test like this

=OR(WEEKDAY(Date)=1,WEEKDAY(Date)=7)

Again, in the following picture we have

  • A list of dates in column A
  • In column B we have the value of the date in word
  • The value returns by the function WEEKDAY (param 2) in column C
  • In column D the result of the test
  • And the formulas in column E

Conditional formatting

Of course, the best is to highlight the day of the weekend. So, you can use exactly the same formula inside a conditional formatting rule.

  • Copy your formula WEEKDAY
  • Select all the cells of your document
  • Open the menu Home>Conditional Formatting>Manage Rules>New Rules
  • Select the option Use a formula to determine which cells to format
  • Paste the formula
  • Change the format

 

 

And the result is

Related posts


Permanent link to this article: https://www.excel-exercise.com/weekends-weekdays-excel/


2 comments

    • Aleks on 24/01/2018 at 20:29
    • Reply

    https://www.excel-exercise.com/the-week-functions/

    Excel Exercice (?)
    Free Excel Exercise online

    The WEEKDAY function:

    The second parameter
    1 - The week starts a Sunday (1) and finish a Monday (7) ?????
    3 - Week starts a Sunday (0) and finish a Monday (6) ?????

    (Too many various mistakes, in this copy-paste I've counted up 2 major and 4 grammatical)

    1. Thanks Aleks for your comment and you're right about the mistakes and the title of the website.
      I have updated the article and I hope the explanations will suit you. But your remark about the value of the second parameter is correct.
      Please, test the second parameter in your worksheet and you will see that the function returns different values in function of value of the second parameter. Of course, you don't need to change this parameter all the time. Find the best value for the second parameter in your worksheet and never change it (I personally always work with the value = 2)

Leave a Reply

Your email address will not be published.