Converting Epidemiology Week to Date, Date to Epidemiology Week

epidweek.txt

EPIDWEEKTODATE=LAMBDA(Yr, EpidWk,
    LET(
        start, DATE(Yr, 1, 1),
        dow, WEEKDAY(start, 12),
        flag, IF(dow >= 4, 0, -1),
        weekstart, start - dow,
        weekstart + (EpidWk + flag) * 7
    )
);
EPIDDATETOWEEK = LAMBDA(Dt,
    LET(
        start,DATE(YEAR(Dt),1,1),
        prevstart,DATE(YEAR(Dt)-1,1,1),
        nextstart,DATE(YEAR(Dt)+1,1,1),
        dow,WEEKDAY(start,12),
        prevdow,weekday(prevstart,12),
        nextdow,WEEKDAY(nextstart,12),
        flag,IF(dow >= 4, 0, 1),
        flagprev,if(prevdow >=4,0,1),
        flagnext,IF(nextdow >= 4, 0, 1),
        weekstart, start - dow,
        prevweekstart,prevstart - prevdow,
        tdow,WEEKDAY(Dt,3),
        thisweekstart,Dt-tdow,
        EpWeek,(thisweekstart-weekstart)/7+flag,
        EpWeek2,if(EpWeek=0,(thisweekstart-prevweekstart)/7+flagprev,EpWeek),
        EpWeek3,if(or(EpWeek2<53,flagnext=0),EpWeek2,1),
        EpWeek3
    )
);
EPIDDATETOYEAR = LAMBDA(Dt,
    LET(
        baseyear,Year(Dt),
        start,DATE(baseyear,1,1),
        prevstart,DATE(baseyear-1,1,1),
        nextstart,DATE(baseyear+1,1,1),
        dow,WEEKDAY(start,12),
        prevdow,weekday(prevstart,12),
        nextdow,WEEKDAY(nextstart,12),
        flag,IF(dow >= 4, 0, 1),
        flagprev,if(prevdow >=4,0,1),
        flagnext,IF(nextdow >= 4, 0, 1),
        weekstart, start - dow,
        prevweekstart,prevstart - prevdow,
        tdow,WEEKDAY(Dt,3),
        thisweekstart,Dt-tdow,
        EpWeek,(thisweekstart-weekstart)/7+flag,
        EpYear,baseyear,
        EpWeek2,if(EpWeek=0,(thisweekstart-prevweekstart)/7+flagprev,EpWeek),
        EpYear2,if(EpWeek=0,baseyear-1,EpYear),
        EpWeek3,if(or(EpWeek2<53,flagnext=0),EpWeek2,1),
        EpYear3,if(or(EpWeek2<53,flagnext=0),EpYear2,EpYear+1),
        EpYear3
    )
);

Converts epidemiological weeks used in infectious disease surveillance to dates and vice versa.
Import and use with Excel's Advanced Formula Environment.

Usage
=EPIDWEEKTODATE(Year, epidemiological week number)
Returns the first date of the corresponding epidemiological week from the Gregorian calendar year and epidemiological week number.

=EPIDDATETOWEEK(date)
Returns the corresponding epidemiological week number from an arbitrary date. Calculate the epidemiological year with the following EPIDDATETOYEAR function.

=EPIDDATETOYEAR(date)
Returns the epidemiological year to which an arbitrary date belongs from an arbitrary date.

Tested by entering actual data from 2000 to 2025.