When entering the date, use the DATE function, as text values might return errors.Must refer to a cell containing a date, number, or function returning a date type. date - The date for which to determine the ISO week number.If it's a Monday, we found the week 1 start date straight away if Tuesday, week 1 starts one day earlier, if Wednesday, two days earlier.Returns the number of the ISO (International Organization for Standardization) week of the year where the provided date falls. If it's Thursday, week 1 starts three days earlier, if Friday, four days earlier, if Saturday, five days earlier, if Sunday, six days earlier. So we calculate 4-Jan and work out which day of the week it is. In other words, that the first week must contain four or more days from the year (if 1-Jan were a Thursday, 4-Jan would be the Sunday, and hence would form week 1). A better way is to see that the ISO week is so defined that the 4-Jan of every year is in week 1. However, calculating the date of the first Thursday is hard. If it were the 1st, week 1 will start on the 29-Dec of the previous year (yes, this is correct: the ISO week 1 for a given year may have dates from the previous year) if the 2nd, week 1 will start on 30-Dec of the previous year if the 3rd, 31-Dec if the 4th, 1-Jan if the 5th, 2-Jan if the 6th, 3-Jan and finally if the first Thursday were the 7th, week 1 would start on 4-Jan. According to standard, the first week of a year is a week with the first Thursday of a year, or which contains the 4th of January. The week number is computed as defined in ISO 8601 - the official way to do so in Europe. The first Thursday of the year will be either the 1st, 2nd, all the way up to the 7th of January. The calculator below computes the week number of a given date by four different methods.
If you play around with the numbers, you'll see how to calculate the date of the Monday for week 1. According to the ISO (International Standards Organization) in document ISO 8601, an ISO week starts on a Monday (which is counted as day 1 of the week), and week 1 for a given year is the week that contains the first Thursday in the year.Ĭalculating the date of ISO week 1 for a given year So, how's it done? How do you calculate the ISO week number for a particular date?įirst, we need to review what the ISO week number is.
Also, as you'll see, there are some boundary conditions that can trip you up. My answer is don't I would doubt that your application's performance profile depends on calculating the week number ultra-quickly. In actuality, the algorithm is very simple, but what seems to happen is that people try and implement it in one line of code. Calculating the ISO week number for a dateĬalculating the ISO week number for a particular date seems to cause problems for some developers.