Week Number is incorrect when I enter 01/01/2021

[Yodelayheewho]

Hello forum!
Attached is a spreadsheet and I've highlighted the cells where the formula results are incorrect.
I have formulas in columns A-D. My weeks begins on Monday. When I enter the Purchase Date of: 01/01/2021, the formula in Column A returns "53", which is correct. It returns "2021" in Column C, which is also correct. However, in Column D ("Week of"), when I enter 01/01/2021 and 01/03/2021, it returns the 'Week of" January 3, 2022. I can't figure out why this is happening. Any help would be so appreciated!

 

[vletm]

Yodelayheewho
Do You use dd/mm/yyyy or mm/dd/yyyy with Your dates?
Sometimes even for Excel with 'mixed settings' is hard to know which has used ... especially with weeks - with formulas.

 

[GraH - Guido]

See attached, using shorter formula
=ISOWEEKNUM([@[Purchase Date]])
=[@[Purchase Date]]-(WEEKDAY([@[Purchase Date]],2))+1

 

[shrivallabha]

If you add 53 weeks to a date in 2021, you are bound to end up in 2022!

=IF(E4="","",E4-WEEKDAY(E4,2)+1)
Use this formula which can give you desired results.
Edit: @GraH - Guido beat me to it.

 

[Yodelayheewho]

See attached, using shorter formula
=ISOWEEKNUM([@[Purchase Date]])
=[@[Purchase Date]]-(WEEKDAY([@[Purchase Date]],2))+1

This worked perfectly. Thank you!

 

[Yodelayheewho]

If you add 53 weeks to a date in 2021, you are bound to end up in 2022!

=IF(E4="","",E4-WEEKDAY(E4,2)+1)
Use this formula which can give you desired results.
Edit: @GraH - Guido beat me to it.

I appreciate your help.