WeekNum function following a 53 week year

[JamesCollins]

Hi I'm looking for some help with the Weeknum function please.
Our work calendar runs Sunday-Saturday, with week 1 this year being Sunday Jan 7th (the first Sunday of January). However Weeknum uses the Week 1 as the first week of January containing a Monday, which is our week 53 for last year.

Many thanks,
James

 

[felixstraube]

It seems correct the result you are getting from Weeknum.
The week starting 31-dic-2023 is the number 53 of the year 2023 and is the number 1 of the year 2024.
By the way most years have the week number 53, and some even 54, like in the year 2000, 2028, 2056 for example.
What do you want to achieve? Do you need the week number to be used in another formula? Could you explain in more detail your problem?

	23/12/2023 Sat	51
	24/12/2023 Sun	52
	25/12/2023 Mon	52
	26/12/2023 Tue	52
	27/12/2023 Wed	52
	28/12/2023 Thu	52
	29/12/2023 Fri	52
	30/12/2023 Sat	52
2023	31/12/2023 Sun	53
2024	01/01/2024 Mon	1
	02/01/2024 Tue	1
	03/01/2024 Wed	1
	04/01/2024 Thu	1
	05/01/2024 Fri	1
	06/01/2024 Sat	1
	07/01/2024 Sun	2
	08/01/2024 Mon	2
	09/01/2024 Tue	2
	10/01/2024 Wed	2
	11/01/2024 Thu	2
	12/01/2024 Fri	2
	13/01/2024 Sat	2
	14/01/2024 Sun	3
	15/01/2024 Mon	3
	16/01/2024 Tue	3
	17/01/2024 Wed	3
	18/01/2024 Thu	3
	19/01/2024 Fri	3
	20/01/2024 Sat	3

 

[JamesCollins]

Hi felixstraube
I need Sunday 31/12/23 to Saturday 6/1/24 to be week 53
Then Week 1 to be Sunday 7/1/24 to Sat 13/1/24 and so on.

 

[felixstraube]

If you have your date in B2 try this formula:

=LET(d,B2,
maxWeekNumberOfLastYear_temp,WEEKNUM(DATE(YEAR(d)-1,12,31)),
currentWeekNumber,WEEKNUM(d),
firstDayOfYearIsSunday,WEEKDAY(DATE(YEAR(d),1,1))=1,
firstDayOfLastYearIsSunday,WEEKDAY(DATE(YEAR(d)-1,1,1))=1,
correctedMaxWeekNumberOfLastYear,IF(firstDayOfLastYearIsSunday,maxWeekNumberOfLastYear_temp,maxWeekNumberOfLastYear_temp-1),
IF(firstDayOfYearIsSunday,currentWeekNumber,IF(currentWeekNumber=1,correctedMaxWeekNumberOfLastYear,currentWeekNumber-1))
)

I checked if it works ok for the next years:

In the file there is a complete list of year till 2046.

WeekNumber.zip


Another thing you could do is just convert the last week to 1 if it is not a complete week like this (also in the file):

The formula for that would be:

=LET(d,B2,
maxWeekNumberOfYear_temp,WEEKNUM(DATE(YEAR(d),12,31)),
currentWeekNumber,WEEKNUM(d),
lastDayOfYearIsSaturday,WEEKDAY(DATE(YEAR(d),12,31))=7,
IF(NOT(lastDayOfYearIsSaturday)*(currentWeekNumber=maxWeekNumberOfYear_temp),1,currentWeekNumber)
)