Ron de Bruin has an ISO Date page for computing ISO Week numbers, start dates, and end dates. I needed to compute the ISO year, which should just be the YEAR() function wrapped around the end date formula. But I was getting strange results for the first three days of January this year, so I modified the formulas
B2: =DATE(YEAR(A2-WEEKDAY(A2-1)+4),1,1)-MOD(DATE(YEAR(A2-WEEKDAY(A2-1)+4),1,1)-2,7)+(7*(MOD(DATE(YEAR(A2-WEEKDAY(A2-1)+4),1,1)-2,7)>3))
C2: =DATE(YEAR(A2-WEEKDAY(A2-1)+4)+1,1,1)-MOD(DATE(YEAR(A2-WEEKDAY(A2-1)+4)+1,1,1)-2,7)+(7*(MOD(DATE(YEAR(A2-WEEKDAY(A2-1)+4)+1,1,1)-2,7)>3))-1
D2: =YEAR(DATE(YEAR(A2-WEEKDAY(A2-1)+4),1,1)-MOD((DATE(YEAR(A2-WEEKDAY(A2-1)+4),1,1)-2),7)+
(7*IF(MOD((DATE(YEAR(A2-WEEKDAY(A2-1)+4),1,1)-2),7)>3,1,0))+7)
They’re the same as Ron’s except it uses the WEEKDAY() offset in the DATE() calculations. The purpose of these is to supply a date and return the start of the ISO year, the end of the ISO year, and the ISO year number, respectively.
Hi Dick
The formula examples on my page are based on the year and you are correct that
you see strange results when the date is before 4-jan-2010 if your input is a date.
Hi Dick
FYI
I will update the page/workbook when I am ready with some stuff I am working now
I have a few other formulas also that are shorter for ISO that I want to add
This is a very timely update (no pun intended). As we become increasingly global in our businesses, having to deal with dates has been quite an interesting ordeal. I also read the original page and found it to be informative and useful.
Thank you for taking the time to share the solution and the spreadsheet.
Before you led me via Ron to D Maher’s & C Pearson’s efficient calculation for ISO Week Number, I had created an elaborate UDF based on VBA’s DatePart function. If anyone else has gone down that road, you should know there is a bug waiting for you.
gives a good ISO week number, except for these dates (in my sample range):
1991-12-30
2003-12-29
2007-12-31
2019-12-30
2031-12-29
The function returns week 53 (day 1). The day following each correctly returns week 01 (day 2).
(I’m Excel 2003 on XP)
There’s another glitch at 2101-01-02, but I haven’t investigated, gonna go with the slick formula instead.
Thanks!
This was a nice formula:
YEAR(date + 26 – ISOWeekNumber)
It seems to work, but I’ve only tried it on a few dates, e.g. 2019-12-30 and 2021-01-01 that have ISO year 2020.
Source:
https://datacornering.com/how-to-calculate-iso-week-number-and-iso-year-in-excel/
PS. I haven’t found any mathematical explanation, and I’m too tired right now to look into that myself…