Week Number in Excel (WEEKNUM)

For example, suppose cell A1 has July 8, 2021, which falls on Thursday. On applying the WEEKNUM Excel function, it will return 28.

=WEEKNUM(A1) 

=28.

You are free to use this image on you website, templates, etc., Please provide us with an attribution linkHow to Provide Attribution?Article Link to be HyperlinkedFor eg:Source: Week Number in Excel (wallstreetmojo.com)

Syntax

  • serial_number: This is the date. Based on this date, we are trying to find the week number of the date. Excel treats DATE as a serial number.return_type: We need to mention the start of the weekday.

We have 10 different options to supply at the start of the week. Since this is an optional argument, we need not mandatorily supply the argument. However, if we leave this argument blank by default, the value will be 1, i.e., the start of the day of the week is Sunday.

The start of the year, Jan 01, will always be the first week of the year, and Dec 31 will always be the last week of the year, the 54th week.

How to Use the Week Number (WEEKNUM) Function in Excel?

Example #1 – Finding Week Number in Excel

Example #2 – Add the Word WEEK to the Week Number in Excel

We have seen how to return the week number of any supplied date. It will be cool to add the word “Week” before the week’s number, like in the image below.

  • Open the Excel WEEKNUM function in the B2 cell. The serial_number is nothing but your date cell. So select A2 as the cell reference. As of now, ignore the last argument. It will default consider Sunday as the starting day of the week. Drag the formula to other cells. We got week numbers for respective dates. But look at the cells A2 and A3; both are on 30th Nov but of a different year. Since Excel considers the start of the week from Sunday, it will vary year to year. Now, let us change the start of the week to MONDAY. You can mention the argument as 2. Close the formula. The result is shown below:Apply to other cells.We do not see any differences here. Try changing the starting day of the week to a different date and find the differences.

The result is shown below:Apply to other cells.We do not see any differences here. Try changing the starting day of the week to a different date and find the differences.

It is where the advanced formatting technique in excelFormatting Technique In ExcelFormatting is a useful feature in Excel that allows you to change the appearance of the data in a worksheet. Formatting can be done in a variety of ways. For example, we can use the styles and format tab on the home tab to change the font of a cell or a table.read more comes into the picture. Follow the below steps to apply the above formatting.

  • Step 1: Select all the result cells first.

  • Step 2: Right-click on the selected cells and select “Format Cells.” You can directly press the shortcut key “Ctrl + 1.”

  • Step 3: Now, we will see below format dialog box. Go to “Custom” first.

  • Step 4: Enter the format code “Week” # in the Type: area.

  • Step 5: Press “OK.” We will have results ready.

Instead of changing the formatting of the cells, we can also combine the word “Week” with the result cells. Refer to the image to apply the same.

Example #3 – Calculate Number of Weeks in excel between Two Dates

We have learned the technique of finding the week number of the supplied technique in Excel. But, how do you tell how many weeks are between two dates?

Assume you started the project on 15th Jan 2018, and you completed the project on 30th April 2018. How do you tell how many weeks you had taken to complete the project?

  • Step 1: Firstly, find the difference between the two dates.

  • Step 2: Now, divide the difference by 7. We have 7 days a week.

  • Step 3: Press the “Enter” key to complete the formula. We have a result like this.

  • Step 4: Change the format to “General” for accurate results.

Example #4 – Calculate the Number of Weeks in Fraction using Excel

Let us consider one more example to have a clear idea about finding the week’s number between two dates in Excel.

You are going on a business trip from 15th Feb 2018. After all the duties are completed, you are coming back on 30th Jun 2018. Your business head decided to pay you the money weekly.

Now, you must tell me how many weeks you are going on a business trip. Let us apply the same formula we have used in the previous example.

Find the difference between two dates.

Now, divide the difference by 7. So we have 7 days a week.

We have a result like this.

Let us change the format of the resulting cell to fractions.

So, we have the number of weeks in fractions now.

Things to Remember

This article is a guide to WEEKNUM in Excel. We discuss finding week numbers in Excel using the WEEKNUM function, practical examples, and a downloadable Excel template. You may learn more about Excel from the following articles: –

  • Weekday in VBAWeekday In VBAWeekday in VBA is a date and time function to identify the weekday of a given date provided as an input. This function returns an integer value from 1 to 7 range. There is an optional argument provided to this function which is the first day of the week.read moreDATEDIF Function in ExcelEDATE Function in ExcelDATEVALUE in ExcelDATEVALUE In ExcelThe DATEVALUE function in Excel shows any given date in absolute format. This function takes an argument in the form of date text normally not represented by Excel as a date and converts it into a format that Excel can recognize as a date.read more