• 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.

Weeknum not showing week 1 in 2022

A

aggie81

Member
I use the formula Weeknum(a1,1) for a date that is January 2, 2022 but it doesn't return week 1 instead it returns week 2.
I have tried various other first week dates January 2, 2022 thru January 8, 2022 and it won't return week 1 but week 2.
I have used the other return_type handles but can't get the desired results: 21,17.
I'm in USA.
The spreadsheet is used for scheduling planting dates for greenhouse crops and the weeks don't coincide with the wall calendar that we use. The wall calendar shows January 1, 2022 in week 52 of 2021 with January 2, 2022 in week 1 of 2022.
If I use the Weeknum() results, then I am a week behind in production.
Thanks,
Lee Coleman
 
That will return the correct week for January 2, 2022 but shifts the other days of that first week into week 2. January 7, 2022 returns a week 2 with the return_type 2. The closest to matching the wall calendar is with return_type 21 but it puts January 2, 2022 into week 52 of year 2021.
 
This is due to the way that Excel calculates the week number. Since January 1 occurs on a Saturday, it throws January 2 into week 2. Perhaps the simple fox for you would be to simply subtract 1 from the result as below.

=WEEKNUM(A1,1)-1

Hope that helps!

Gig 'em!
Ken ('92)
 
aggie81
You could choose suitable return_type (top row) from below snapshot ( the 1st is without return_type ) :
Screenshot 2021-12-28 at 19.26.29.png
Ps. There are more possibilities with VBA.
 
Weeknum(date, 21) returns the isoweeknum. Or in later versions there is isoweeknum(date). That uses a week starting on Monday though.
 
GraH - Guido
Isn't Monday good day to start a week?
... or have You named any other possible weekday?
With Weeknum other return_types than 21 seems to give ... week 53 for the last week of 2021 and week length seems to vary.
... with VBA more possibilities.
 
You must log in or register to reply here.
Back
Top