# Find Weekend or Weekday in Excel

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)

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

## 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

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

## 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.

## Frédéric LE GUEN

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 ___________________

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

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

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

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)

• 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)

# Find Weekend or Weekday in Excel

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)

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

## 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

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

## 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.

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 ___________________

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

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

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

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)

• 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)