Contents
The need to know the number of the working week for a given date (or group of dates) is a very popular thing in management accounting. Weekly reports, weekly monitoring of the implementation of the plan, weekly broadcast schedule in advertising – all this requires the ability to determine the number of the working week for a given date. The task, at first glance, is simple, but (as always) there are several very significant nuances.
Firstly, in different countries, the countdown of the beginning and end of the week itself is different. In Our Country, Monday is the day of reference for the week, while in other countries (for example, the USA and Israel) it is Sunday.
Secondly, the question is which week is considered the first? At the moment, there are at least two main options with different logic. Let’s consider them sequentially.
Method 1. Standard GOST ISO 8601-2001 and function NOMNEDELI.ISO
The first working week of the year is the one that falls on the first Thursday of the year (or January 4, if you prefer). Here the logic is simple. The first week is the one that has more than three days (more than half a week) of the coming year. Some inconvenience is that in a year it turns out when 52, and when 53 working weeks. Plus, January 1 could easily be the 52nd week of the previous year.
But it is this option that has been officially adopted in Our Country since 2002 as the state one and remains so at the moment (see GOST ISO 8601-2001). To calculate the week number by date using such a system, you can use the following formula:
=ОТБР(ОСТАТ(A1+3-ДЕНЬНЕД(A1;2);365,25)/7+1)
=TRUNC(MOD(A1+3-WEEKDAY(A1;2);365,25)/7+1)
In Excel 2013, a function for calculating the ISO week number was added to the standard set – it is called NOMNEDELI.ISO (WEEKNUM.ISO)
Method 2. Week since January 1st and WEEKNUM function
In this case, the first week of the year is the one that falls on January 1st. Here, too, a difficulty arises in that there may be 52 or 53 weeks in a year and, in addition to everything, January 1 may fall on a Sunday, i.e. the last six days of the year may already be in week 1, making reporting difficult. However, if you need such a numbering method, then it can be implemented with the function NOMNEDELI (WEEKNUM), which has been in Excel since 2007. The syntax for this function is:
=NOMNEDELI(date; count_type)
where
- date – a cell with a date for which you want to determine the week number
- count_type — a number (1, 2 or 3) indicating the numbering type of the days in the week. Option 1 is American (the week starts on Sunday), option 2 is ours.
- Fundamentals of working with dates and times in Microsoft Excel
- Quarter number by date
- Calculation of age or length of service in years, months, days
- Drop-down calendar for easy date entry from PLEX add-on