Following the popularity of my blog post last week about How to Calculate the Number of Days Between Two Dates (which even attracted the attention of The Official Twitter Page for Excel!), I am continuing the series with an introduction to the WEEKNUM & ISOWEEKNUM functions.
Why Do I Need to Know the Week Number
Many companies work to weeks instead of actual dates. This can be for a number of different reasons, but a common reason is so as not to commit to a specific day/date as there can be many factors to consider.
There can often be delays in production, or delivery of raw materials, or “Acts of God” that could delay the despatch of an order. Because of this, many companies would state something like “Despatch week commencing 12th December 2016” or maybe “ Despatch week 50” – I know this is certainly what my company (a paper mill) does and many of our suppliers/customers do the same.
How to Calculate the Week Number
Quite simply put, Week 1 is the first week of the year. Although confusion can arise depending on which day of the week the 1st January falls. Week 2 is the following week and so on. You can read up on the rules here.
This simple function in Excel has since been superseded by ISOWEEKNUM in Excel 2013 and above due to a bug for the year 2016 – but more on that later!
=WEEKNUM(Date) is this function in its simplest form. But please be aware that the date does need to be in serial form (the day number counting from 1st January 1900) – Excel stores dates as sequential serial numbers so that they can be used in calculations. By default, January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,447 days after January 1, 1900.
Or you can use a nested DATE(year,month,day) to identify the date:
The easiest way to tackle this is to have the date in a cell and reference that cell – in reality, this is the most common and useful way. It should look something like this:
By default the first day of a week is Sunday, but this can be changed by adding the ‘return type’ to the end of the WEEKNUM formula. Generally, you will only need option 1 (Sunday) or 2 (Monday)
The year 2016 presented a bug with the WEEKNUM function – if you want to read more about it, I am sure there is stuff to be found on Google. But the important thing is that Microsoft fixed it for us with the introduction of ISOWEEKNUM.
In fact, ISOWEEKNUM is actually even simpler than its predecessor as it takes away the need to define the start day of the week and runs on the ISO Week Number rules.
Again I would recommend calling the date from a referenced cell:
Remember that this function is only available in Excel 2013 and above!
Wait – does this mean that the function is useless in older versions of Excel?
Not completely, No!
If you use WEEKNUM to determine the week number in Excel 2010, for example, you will find that your result is one week over the desired result. You can of course just subtract 1 from the end of your formula:
=WEEKNUM(Date)-1 – ensure you format your cell to ‘General’ for this to work
Whilst this seems to work well in 2016, I cannot guarantee that it is flawless and of course you may run into issues in future years.
I do however have a great little trick that will bring the ISOWEEKNUM functionality to older versions of Excel!
Create Your Own Custom Excel Function
Now this is really cool, and if you are like me you will love being able to do something like this!
If you would like to add ISOWEEKNUM functionality to your older version of Excel you can do so relatively simply by adding the following VBA to your workbook:
Public Function IsoWeekNumber(d1 As Date) As Integer
‘ Attributed to Daniel Maher
Dim d2 As Long
d2 = DateSerial(Year(d1 – Weekday(d1 – 1) + 4), 1, 3)
IsoWeekNumber = Int((d1 – d2 + Weekday(d2) + 5) / 7)
*Note the Attribution above – much as I would love to claim credit for this amazing bit of VBA, I must hand all praise to Daniel Maher. Find out more about the ins and outs of the code here.
In short, this is how to implement:
- From your workbook press ALT + F11 to open the VBA editor
- Create a new module if you need to and paste the above code to the module.
- Now you have created a new function on Excel – ISOWEEKNUMBER
This new function works in exactly the same way as ISOWEEKNUM, so check above for how to use.
Remember that you will now need to save your workbook as a macro-enabled workbook or .xlsm.
Bear in mind that this custom function will only work when the VBA code is available, so it will need to be embedding into each worksheet required.
‘Installing’ ISOWEEKNUMBER to your computer
It is possible to integrate ISOWEEKNUMBER into your Excel. This can be done in one of two ways:
- Adding to a module in your PERSONAL.XLSB. You will find this is VBA Editor. By doing this you will always have this functionality on your PC. However, remember that this will need to be done on all computers that the function is used.
- Create an Excel Add-in. (.XLAM)
- Open a new workbook
- Open VBA Editor (ALT + F11)
- Insert a new module
- Paste in the code
- Save this workbook as ISOweeknum.xla or Isoweeknum.xlam (depending on your excel version) to C:\Users\[User]\AppData\Roaming\Microsoft\AddIns – Excel will probably direct you here when you choose the add-in extension.
- Enable your new Excel add-in
- Click File – Options, then select Add-ins from the menu on the right
- At the bottom click ‘Go…’ to manage Excel Add-ins
- If you cannot see your Add-in, click browse and find your .xlam
- Tick your Add-in and click ok.
- You now have full ISOWEEKNUM functionality on this PC via your custom function.
- Distribute this .xlam file to all users to add functionality to their PCs.
And that about it. So if you are on Excel 2013 or above the built in ISOWEEKNUM function is nice and simple for you to use. However, if you are still on an older version, you may have to manipulate the WEEKNUM formula to ensure accuracy for for the more adventurous of you, the VBA workaround to create a custome function is a God-send.
I hope you found this little guide useful?
More to come soon!