Selected Answer

Cartus (/Dr Liss)

**REVISION 2**: Answer modified to 1) use date number in formula 2) adjust for erroneous result in week 52 and 3) attach a file comparing formule for all dates in 2021

You can avoid the CHOOSE and ROUNDUP functions if you re-write your formula to something like that below (where you might need to change the date in bold to suit your fiscal year start):

`=MOD(WEEKNUM(B2,1)-WEEKNUM(`**44408**,1),52)

(and that cell should be formatted as General). That gives the same result as yours for today's date for example (even with a 31 July 2021 date number in bold since MOD ignores the negative portion of the remainder it produces).

That does however produce a 0 result when the date in B2 is at the end of the year (so the sum is 52 and the MOD remainder becomes 0.). That can be corrected by wrapping it with *IF (formula<>0, formula, 0) *like this:

`=IF(MOD(WEEKNUM(B2,1)-WEEKNUM(`**44408**,1),52)<>0,MOD(WEEKNUM(B2,1)-WEEKNUM(**44408**,1),52),52)

Then you might use the Power Query function *Date.WeekOfYear* instead of WEEKNUM and *Number.Mod *to replace MOD. Not sure but suggest you check out both sets of Excel and M Query functions since they seem to offer a simpler formula.

**REVISION 1**: See comparison of formulae (D3:D5 for basic with B2 and row 7 down for all dates in 2021) in attached file.