ISOYEAR and ISOWEEK in Excel using formulas
/What is the problem?
Excel has a =ISOWEEKNUM() formula, however I often want the year included and the final format to be something like “1951” if the year was 2019 and week number was 51.
To do this, we need the ISOYEAR. For example 31-Dec-2019, needs to be reported as the year 2020. The normal =YEAR() formula doesn’t have an ISO feature.
ISOWEEK
Uses Today date
=TEXT(ISOWEEKNUM(TODAY()),"00")
Uses Date in E2
=TEXT(ISOWEEKNUM(E2),"00")
ISOYEAR
Uses Today date
=YEAR(DATE(YEAR(TODAY()-WEEKDAY(TODAY()-1)+4),1,1)-MOD((DATE(YEAR(TODAY()-WEEKDAY(TODAY()-1)+4),1,1)-2),7)+(7*IF(MOD((DATE(YEAR(TODAY()-WEEKDAY(TODAY()-1)+4),1,1)-2),7)>3,1,0))+7)
Uses Date in E2
=YEAR(DATE(YEAR(E2-WEEKDAY(E2-1)+4),1,1)-MOD((DATE(YEAR(E2-WEEKDAY(E2-1)+4),1,1)-2),7)+(7*IF(MOD((DATE(YEAR(E2-WEEKDAY(E2-1)+4),1,1)-2),7)>3,1,0))+7)
ISOYEAR and ISOWEEK
Uses Today date
=NUMBERVALUE(CONCAT(RIGHT(YEAR(DATE(YEAR(TODAY()-WEEKDAY(TODAY()-1)+4),1,1)-MOD((DATE(YEAR(TODAY()-WEEKDAY(TODAY()-1)+4),1,1)-2),7)+(7*IF(MOD((DATE(YEAR(TODAY()-WEEKDAY(TODAY()-1)+4),1,1)-2),7)>3,1,0))+7),2),TEXT(ISOWEEKNUM(TODAY()),"00")))
Uses Date in E2
=NUMBERVALUE(CONCAT(RIGHT(YEAR(DATE(YEAR(E2-WEEKDAY(E2-1)+4),1,1)-MOD((DATE(YEAR(E2-WEEKDAY(E2-1)+4),1,1)-2),7)+(7*IF(MOD((DATE(YEAR(E2-WEEKDAY(E2-1)+4),1,1)-2),7)>3,1,0))+7),2),TEXT(ISOWEEKNUM(E2),"00")))
The NUMBERVALUE is needed if you want to use it as a number, else text will be fine. This could be the reason while your lookups are having problem.