Find Weekend or Weekday in Excel

Find Weekend or Weekday in Excel
Last Updated on 14/11/2023
Reading time: 2 minutes

Weekend or weekday are very easy to identify in your worksheet with the same function; WEEKDAY

  1. Understand how the WEEKDAY function works

    This function returns a value representing the day of the week, ranging from 1 to 7

  2. Build your test by using the second argument of the WEEKDAY function

    The WEEKDAY function could be difficult to use if you don't set the second argument

What is the trick with the second argument of the WEEKDAY function?

In the article on the WEEKDAY function, we have seen that the default value of the second argument is 1.

But if you set the value to 2, your calculations with this function will be much easier 😀👍

=WEEKDAY(Date,2)

WEEKDAY return different result for Monday

Applying this formula to any date ensures a consistent result between 1 (Monday) and 7 (Sunday)

What is the result returns by WEEKDAY for each day of the week

How to test if a date is a weekend?

To check if a date is a weekend or not, you can write this simple test

=WEEKDAY(Date,2)>5

  • If the WEEKDAY function returns 6, it indicates Saturday, and 7 denotes Sunday.
  • Utilizing this formula, you can effortlessly determine whether a day falls on a weekend or weekday
Test to check if a date is a weekend or not

Omitting the second argument in WEEKDAY makes the test more complex, requiring an OR function to check for 1 or 7.

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

Test if a date is a weekday

In the same way, to test if a date is a weekday or not, you can write this formula. This time, you must include the value 5 (Friday) in your result using the 'less than or equal to (<=)' operator.

=WEEKDAY(Date,2)<=5

Does a date is a weekday

Conditional formatting

To highlight the day of the weekend in RED you can use exactly the same formula inside a conditional formatting rule.

  1. Copy your test (weekend or weekday)
  2. Select all the cells of your document
  3. Open the menu Home>Conditional Formatting>Manage Rules>New Rules
  4. Select the option Use a formula to determine which cells to format
  5. Paste the formula
  6. Change the format

With this trick, you can highlight the entire row when it's the weekend.

Conditional formatting to change the color of the weekend in red

4 Comments

  1. Samir Ayoub
    19/07/2019 @ 14:38

    In Excel version 2010 and higher, new values have been added but it's not really useful except for some #Arabic countries where the weekend is not Saturday and Sunday.
    ________ FRIDAY ___________________

    =WEEKDAY(C$6,16)>6

    ________ FRIDAY ___________________

    Reply

  2. Ndombi
    13/08/2018 @ 08:05

    Thank you for this. The simple explanation makes the function a breeze. Kudos.

    Reply

  3. Aleks
    24/01/2018 @ 20:29

    https://excel-tutorial.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)

    Reply

    • Frédéric LE GUEN
      25/01/2018 @ 15:05

      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)

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Find Weekend or Weekday in Excel

Reading time: 2 minutes
Last Updated on 14/11/2023

Weekend or weekday are very easy to identify in your worksheet with the same function; WEEKDAY

  1. Understand how the WEEKDAY function works

    This function returns a value representing the day of the week, ranging from 1 to 7

  2. Build your test by using the second argument of the WEEKDAY function

    The WEEKDAY function could be difficult to use if you don't set the second argument

What is the trick with the second argument of the WEEKDAY function?

In the article on the WEEKDAY function, we have seen that the default value of the second argument is 1.

But if you set the value to 2, your calculations with this function will be much easier 😀👍

=WEEKDAY(Date,2)

WEEKDAY return different result for Monday

Applying this formula to any date ensures a consistent result between 1 (Monday) and 7 (Sunday)

What is the result returns by WEEKDAY for each day of the week

How to test if a date is a weekend?

To check if a date is a weekend or not, you can write this simple test

=WEEKDAY(Date,2)>5

  • If the WEEKDAY function returns 6, it indicates Saturday, and 7 denotes Sunday.
  • Utilizing this formula, you can effortlessly determine whether a day falls on a weekend or weekday
Test to check if a date is a weekend or not

Omitting the second argument in WEEKDAY makes the test more complex, requiring an OR function to check for 1 or 7.

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

Test if a date is a weekday

In the same way, to test if a date is a weekday or not, you can write this formula. This time, you must include the value 5 (Friday) in your result using the 'less than or equal to (<=)' operator.

=WEEKDAY(Date,2)<=5

Does a date is a weekday

Conditional formatting

To highlight the day of the weekend in RED you can use exactly the same formula inside a conditional formatting rule.

  1. Copy your test (weekend or weekday)
  2. Select all the cells of your document
  3. Open the menu Home>Conditional Formatting>Manage Rules>New Rules
  4. Select the option Use a formula to determine which cells to format
  5. Paste the formula
  6. Change the format

With this trick, you can highlight the entire row when it's the weekend.

Conditional formatting to change the color of the weekend in red

4 Comments

  1. Samir Ayoub
    19/07/2019 @ 14:38

    In Excel version 2010 and higher, new values have been added but it's not really useful except for some #Arabic countries where the weekend is not Saturday and Sunday.
    ________ FRIDAY ___________________

    =WEEKDAY(C$6,16)>6

    ________ FRIDAY ___________________

    Reply

  2. Ndombi
    13/08/2018 @ 08:05

    Thank you for this. The simple explanation makes the function a breeze. Kudos.

    Reply

  3. Aleks
    24/01/2018 @ 20:29

    https://excel-tutorial.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)

    Reply

    • Frédéric LE GUEN
      25/01/2018 @ 15:05

      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)

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *