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