«

»

The WEEK functions

Problems of calculation with week

The first problem it's the weekends (you know, these days we stay quiet at home watching TV). We can not include these days in a work day calculation.

And do you know that for some countries in the world, the weekend is not Saturday and Sunday? Yes, some Arabic countries have their weekends Thursday and Friday.

But, for Excel, it's not a problem. All this aspect can be solved easily.

Second, the week number The United States and Canada do not have the same method of calculation for the week number as other countries in the world. And here again, there is a way to calculate the week number for the USA and the rest of the world.

The WEEKDAY function

Presentation

The very first function in connection with the week it's the function WEEKDAY. 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 perform a test in a IF function or for a conditional formatting.

Live worksheet

Look at the following spreadsheet. The first function is NOW and the result return the value of the day according to the value of the second parameter.

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 forgot to specify this parameter, if the day of a date is Sunday, the function will return 1 (and 7 for Saturday).

But for most countries in the world, the first day of the week is Monday. So you have to set this parameter in function of the result you want to return.

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

  • 1 - The week starts a Sunday (1) and finish a Monday (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)

For the version of Excel 2010 and the earlier version, new values have been added but honestly. That's necessary if the weekend is not Saturday and Sunday like some Arabic countries.

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

Ok, so what is the interest to have a value between 1 and 7. 🤔🤔🤔

Well, it's very useful in a test.😉😊😃

How to test if a date is a weekend ?

If you want to know if a date is a weekend or not, you write this simple test with the value 2 😉

=WEEKDAY(Date,2)>5

Otherwise, 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)

In this picture, we have

  • A date
  • The day of the date
  • The value returns by the function WEEKDAY
  • Result of the test
  • Formula of the test (it's always the same)

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 articles


Have a look at these other articles that could help you in your work

Permanent link to this article: https://www.excel-exercise.com/the-week-functions/


Leave a Reply

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