Pad week numbers with zeros
In this tutorial, you will find out how to pad week numbers with zeros. As week number can be 2 digits number, we want to add leading zeroes if weeks is 1 – 9. This is possible with the combination of functions WEEKNUM and TEXT. We will explain how both functions work and finally how to get pad week numbers with zeros.
First, we will see how WEEKNUM function works. The purpose of this function is to return a number of a week in a year for a given date. The syntax looks like:
= WEEKNUM(serial_number, return_type)
Serial_number is a cell which contains a date for which we want to get a week number. Be aware that the cell which is sent to the function must be formatted as a date. Return_type tells the function which type of calendar to use. There are 3 possible values of return type:
- 1 – if the return type is 1, week 1 begins on January 1, while the second week begins on the next Sunday;
- 2 – if the return type is 2, week 1 begins on January 1, while the second week begins on the next Monday;
- 21 – if the return type is 21 the function uses ISO week numbers, which means that the first week starts on Monday in period December 29 – January 4.
Please notice that return type is not an obligatory parameter of the function, therefore if omitted value 1 will be a default value. In our example, we will use ISO week numbers, 21.
Let’s now look at TEXT function. This function formats selected data in a needed format, by adding leading zero. This means that we can pad a number with a number of specified zeroes. The syntax looks like:
Value is a selected cell which value we want to format, while format_text is a certain format which we want to apply to a cell. For example, if we want our data always to have length 5, we will put “00000”. By doing this, all data with less than 5 numbers will have the necessary leading added until the length of five.
Now we can go to our example and show how to pad week numbers with zeroes. We want to get the week number of the date in column B with leading zeroes in column C. The formula is:
Figure 1. Pad week numbers with zeros
As you can see, WEEKNUM formula will return number 8 for the B3 cell, because 24-Feb-18 is the 8. week of the year. Furthermore, function TEXT will return 08 in C3, because value 8 is formatted with the format “00”, 2 digits.