Date

Weekends or weekdays in Excel

Reading Time: 3 minutes

Separating weekends and weekdays is a common task in Excel. For example, when you have to build an automatic calendar.

The function WEEKDAY

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

=WEEKDAY(Date,Parameter)

This function is very, very, VERY useful for creating 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 on Sunday (value 1) and finishes on Monday (value 7). It's the default value (or empty)
  • 2 - The week starts on Monday (1) and finishes on Sunday (7)
  • 3 - The week starts on Sunday (0) and finishes on Monday (6)

The screenshot below displays the different results returned for the same date (01/01/2018) in function of the second parameter chosen.

How to test if a date is a weekend?

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

=WEEKDAY(Date,2)>5

With this test, if the day of the week is 6 or 7, it's a weekend 😃

If you don't want to specify the second parameter, Sunday = 1 and Saturday = 7. So the test is a little more complex to write.

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

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

Now, when the day of your dates is a Saturday or Sunday, the background of cells is red.

Add only weekdays

Another very useful week function in Excel it's the WORKDAY function.

When you add days to a date, Excel will always include the weekend. For example, when you add 4 days to the 21/04/2021 (Wednesday), the result is Sunday the 25th

Add 4 days to a date

But, if you want to add 4 weekdays to your original date, WORKDAY will return Tuesday the 27th. Saturday and Sunday have been not included. This function is very useful to build a task calendar or a Gantt Chart.

=WORKDAY(A2,B2)

Add 4 workdays to a date

Number of weekdays between 2 dates

The third week function in Excel is the function NETWORDAYS. It returns the number of weekdays between 2 dates.

=NETWORKDAYS(A2,B2)

Number of workdays between 2 dates

Related posts

Add Days Excluding the Weekend

Frédéric LE GUEN

Difference between 2 dates – DATEDIF

Frédéric LE GUEN

Date Format in Excel

Frédéric LE GUEN

4 comments

Samir Ayoub 19/07/2019 at 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
Ndombi 13/08/2018 at 08:05

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

Reply
Aleks 24/01/2018 at 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)

Reply
Frédéric LE GUEN 25/01/2018 at 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 Comment