Calculating The Week Number In Excel

weeknumWe've just past June 30, so it must be Week 26 of the year, right? The answer turns out to be "maybe", especially if you're running Excel.

Excel has a WEEKNUM function for calculating the week number for a given date, but the results it gives depend on whether you consider that the week starts on a Monday or a Sunday. (The Excel default is to assume Sunday.) If you're in the Monday camp, then this is actually week 27.

As the Microsoft Excel blog points out, the actual ISO standard for week numbering is substantially different again, counting Week 1 of the year as beginning on the first Monday of a week which has a Thursday in it, rather than just on January 1. And there's another method of week counting which ignores weekdays altogether (week 1 begins on January 1, week 2 on January 8 and so forth, with an inevitable short week 53 at the end of the year).

Fortunately, automation means we don't have to worry about this stuff too much (provided we've agreed with our colleagues which system to use). Hit the post for a handy spreadsheet that lets you print out a week-numbered calendar for any year using any of the four approaches, and formulas to use if you want to take the ISO approach instead.

Week Numbers in Excel [Microsoft Excel Blog]


Comments

    I was interested to read what week the 31st of Dec and 1st of January fall into.

    Seems the European standard is Week 1 starts with the first week with 4+ days within. If using the Sunday rule, the week containing the 1st of Jan this year was a Thursday meaning that week only had Thur, Fri and Sat, therefore not matching the 4+ rule, so becomes week 53 of 2008. The first week would began on Sunday 4th of Jan.

      Not quite -- the European (ISO) standard also specifies the week starts on a Monday, so this scenario wouldn't happen.

        Actually it does. If the week begins on a Monday, the week with 1st of Jan would be week one, containing 4 days from January with Thu, Fri, Sat and Sun.

        Angus,

        I did some tests and your initial findings may have been incorrect. The correct format for WEEKNUM is to include a full date in the first field.

        Therefore the following
        =WEEKNUM(DATE(2009,07,01),1)
        =WEEKNUM(DATE(2009,07,01),2)
        return the same result.

Join the discussion!

Trending Stories Right Now