WEEKNUM, ISOWEEKNUM,WEEKDAY Excel – Was it on Friday After 15th Week of The Year?

Last modified date:

You have a huge table with many date entries in it. You need to find out which date is after 20th week of the year or if a certain date is Friday. MS Excel will help by three functions – WEEKNUM, ISOWEEKNUM,WEEKDAY . It is definitely more effective than searching in calendar. More so if you have a real lot of data. The premise for use of these function in Date and time group are cells containing data type Date.

ISOWEEKNUM and WEEKNUM

Let’s start with function ISOWEEKNUM. Its syntaxe is very simple:

=ISOWEEKNUM(cell)

and you do not need anything else. Either enter the date into quotes or link to the cell. The result of the function is the number of the week in year according to ISO 8601 standard. This means that first week of the year is the one containing at least one workday. New year must be on Thursday and 2nd of January is workday. If the New year falls on Sunday, the 1st week starts on 1st Monday.

Back to Excel. For example for formula =ISOWEEKNUM(“17.6.2014”) you will get 25 as result. Meaning that the entered date was in 25th week of the year. That’s all. This function calculates with default Excel settings. For Czech location, it means that the week starts by Monday. But what about Anglo-Saxon lands where the week starts by Sunday? We will use function WEEKNUM.

Function WEEKNUM also returns number of the week in the year but it is possible to state which system will be used to calculate the week number. Function haves this syntax:

=WEEKNUM(cell;type)

and the code is determining the number of the week calculation system according to this key:

type Week begins with System
1 či neuveden Sunday 1
2 Monday 1
11 Monday 1
12 Tuesday 1
13 Wednesday 1
14 Thursday 1
15 Friday 1
16 Saturday 1
17 Sunday 1
21 Monday 2

Lets explain this table. Type means given type in the function formula WEEKNUM. System 1 or 2 means following:

System 1 – first week marked by number 1 is the week in which the data 1st January is present

System 2 – is according to ISO 8601 norm, also called European week notation system

Parameter type is optional.

WEEKDAY – It is Friday or Tuesday

WEEKDAY function is simple. It returns serial number of day in week to entered date. Function is guided by local Excel version. Formula syntaxe is

=WEEKDAY(cell)

and the result is simply a serial number of the day in week. Meaning Tuesday will have 2 and Saturday 6 (according to our start of the week).

My name is Jan Zedníček and I work as a BI Developer at Kentico Software in Brno. Mostly you can see me there working in the office, but I also work partly as a freelancer. I have been working as a freelancer for many companies for more than 5 years, but Kentico is the matter of my heart. I used to work as a financial controller at companies like Aero Vodochody or Sberbank and I also used to be a bond program manager in Unicapital Investment group. When I am not at work, I like playing volleyball, chess, doing a workout in the gym and I enjoy tasting of best quality rums. I am trying to summarize all my knowledge on this website not to forget them (because of the rum effect, you know =) and to put them forward to anyone. Don´t worry about asking for help or writing some comments.

Category: Excel functions Tags:

About Ing. Jan Zedníček - BI Developer, Finance controller

My name is Jan Zedníček and I work as a BI Developer at Kentico Software in Brno. Mostly you can see me there working in the office, but I also work partly as a freelancer. I have been working as a freelancer for many companies for more than 5 years, but Kentico is the matter of my heart. I used to work as a financial controller at companies like Aero Vodochody or Sberbank and I also used to be a bond program manager in Unicapital Investment group. When I am not at work, I like playing volleyball, chess, doing a workout in the gym and I enjoy tasting of best quality rums. I am trying to summarize all my knowledge on this website not to forget them (because of the rum effect, you know =) and to put them forward to anyone. Don´t worry about asking for help or writing some comments.

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.