Excel weeknum function returns wrong week

Question

Question is as in title.

I have a cell, "D4", with the date "09/07/2016" in it. Adjacent cell has formula "=weeknum(D4,1)". The output of this function is "28". But on a Sunday-Saturday basis, Saturday the 9th of July wasn't in week 28 - it was in week 27.

I thought this might be something to do with Saturday/Sunday and when the week starts and finishes, etc, so I tried multiple different dates from last week - Monday the 4th, Tuesday the 5th, Wednesday the 6th, etc. In each case, "weeknum" returns a value of "28".

I only noticed the problem because I have a macro which uses the value of the cell with the week number to look for a spreadsheet saved by our accounts team on a weekly basis. As they have - correctly - saved the spreadsheet as "week 27", it didn't work. I initially assumed that the accounts team were wrong, but I checked online and they are correct.

How could this happen? Surely Excel can't be wrong and I must have made a mistake of some sort?

I know that there is an issue with Excel not following ISO standards for when weeks 53 and 1 begin and end, but I don't see how that could affect a mid-year week.

It did occur to me that the issue might be to do with UK versus US date formatting. But, of course, the 7th of June wasn't in week 28 either.

I see week 28 starting on July 3rd, 2016 and ending on July 9th, 2016 on a Sunday-Saturday basis... — Scott Holtzman
– Scott Holtzman, Commented Jul 14, 2016 at 14:44
My guess would be that excel takes January 1st as falling in the first week, while you probably want to start counting from the first full week (I don't know about ISO standards). If that's the case, Excel would add an extra week whenever January 1st falls in an incomplete week. — NiH
– NiH, Commented Jul 14, 2016 at 14:49
Scott - interesting. Why? week-number.net/calendar-with-week-numbers-2016.html epochconverter.com/weeks/2016 — Statsanalyst
– Statsanalyst, Commented Jul 14, 2016 at 15:02
@Statsanalyst: "I know that there is an issue with Excel not following ISO standards for when weeks 53 and 1 begin and end, but I don't see how that could affect a mid-year week." One week is following the previous without any buffer;-). So if you push the first week one week number to the right, all following weeks will follow. — Axel Richter
– Axel Richter, Commented Jul 14, 2016 at 15:44

Axel Richter · Accepted Answer · 2016-07-14 15:22:48Z

14

That all is documented in WEEKNUM.

There are two systems used for this function:

System 1 The week containing January 1 is the first week of the year, and is numbered week 1.

System 2 The week containing the first Thursday of the year is the first week of the year, and is numbered as week 1. This system is the methodology specified in ISO 8601, which is commonly known as the European week numbering system.

Syntax

WEEKNUM(serial_number,[return_type])

Return_type  Week begins on  System 
...
21           Monday          2

So =WEEKNUM("09/07/2016",21) will calculate as defined in ISO 8601 since Return_type 21 is the only one with System 2.

ISOWEEKNUM

=ISOWEEKNUM("09/07/2016")

will also do it.

edited Jul 14, 2016 at 15:22

answered Jul 14, 2016 at 15:17

Axel Richter

62.5k7 gold badges70 silver badges95 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jens · Accepted Answer · 2016-09-01 08:50:00Z

1

=ISOWEEKNUM(a2)-1 returns the correct week for me, the same as =WEEKNUM(a2,21)-1

edited Sep 1, 2016 at 8:50

Jens

69.6k15 gold badges107 silver badges133 bronze badges

answered Sep 1, 2016 at 8:22

wobba

111 bronze badge

Collectives™ on Stack Overflow

Excel weeknum function returns wrong week

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related