• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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

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!
 

Attachments

  • Week Number.xlsx
    12.4 KB · Views: 5
See attached, using shorter formula
=ISOWEEKNUM([@[Purchase Date]])
=[@[Purchase Date]]-(WEEKDAY([@[Purchase Date]],2))+1
 

Attachments

  • Week Number First date of week.xlsx
    13 KB · Views: 5
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.
 
Back
Top