WEEKNUM() returning 01/01/2021 as Week 01

Hi!

Based on what I have read, WEEKNUM() takes 31/12 as week 53 and 01/01 as week 01. Here in AU, week 01 starts on 04/01. How can I make the WEEKNUM() to return the 04th as Week 01 instead of 02?
I appreciate any help you can provide.
Thank you

1 Like

That may be a bug actually.
@Steve what do you think? Provided we are in the same page, can please escalate this to the dev team? Thnx.

1 Like

I had a client tell me last week that they were getting different results for WEEKNUM from AppSheet vs GSheets or Excel. I’ll see if I can get some more info for troubleshooting.

2 Likes

Expression:
image

Output:
image

Definition of week number:

Escalated.

6 Likes

We have been having this same issue, but I’m not sure it’s going to be an easy problem to solve. Here are some of the conflicts I’ve come across:
Example:
Android calendars show 1/11/2021 = week 3
Google calendars show 1/11/2021 = week 2
Apple calendars show 1/11/2021 = week 2
Excel, and gsheets =weeknum(today)) = week 3 (today being 1/11/2021)

Thanks for your response.
In the meantime, is there an alternative expression to calculate the ISO week number in Appsheet?
The week is crucial for my app since the company procedures are run per week. :frowning:
TIA

WEEKNUM([Date]-3) should give a comparable result although I would test to make sure. Another would be find the day of the year, minus those 3 days and then divide by 7 then add 1.
The second formula would be harder but would continue to work for the remainder of the current year while the first is easier. Until Appsheet can figure out their bug here :eyes:

2 Likes

There is not. :frowning:

1 Like

Gracias! I will try it. :slight_smile:

You can try this calculation and see if it returns the correct results:
FLOOR(10 + ([date] - DATE("1/1/" & YEAR([date]))) - WEEKDAY([date] - 1) / 7)

2 Likes

Thanks everyone for your contributions.
After some attempts, the expression below seems to do the trick. :slight_smile:

CEILING( (WEEKNUM([Date])) - (((WEEKDAY([Date] - 1)) / 7 + 1)))

I still hope Apssheet give us an ISO alternative for these type of cases. :grimacing:

3 Likes

@Alejandra_Petro
Just a sweet reminder;
Provided your WEEKNUM([Date]) exression returns a value of 52, 53 or 1, your expression will return the arithmetic value of 51, 52 or 0. For example:

[Date] = 1/1/2021

CEILING(WEEKNUM([Date]) - (((WEEKDAY([Date] - 1)) / 7 + 1)))

= CEILING(1 - (((WEEKDAY([Date] - 1)) / 7 + 1)))
= 0

2 Likes

Hi @LeventK, thanks for the reminder.

I am aware of this. It will still help my App to get the right week while the AppSheet team provides a better solution :slightly_smiling_face:

@Alejandra_Petro
Until then, below expression will work as an exact match as per ISO Week Date format

IF(
	MOD(
		YEAR([TIMESTAMP]),
		4
	)=0,
	FLOOR( 
		(10 +
		IF(
			MONTH([TIMESTAMP])=1,
			0,
			((HOUR(
				EOMONTH([TIMESTAMP],-1)	- DATE("1/1/"&YEAR([TIMESTAMP]))
			)/24) + 1)
		)
		+ DAY([TIMESTAMP])
		- (WEEKDAY([TIMESTAMP]) - 1)		
		) / 7
	),
	FLOOR(
		(10 +
		IF(
			MONTH([TIMESTAMP])=1,
			0,
			HOUR(
				EOMONTH([TIMESTAMP],-1)	- DATE("1/1/"&YEAR([TIMESTAMP]))
			)/24
		)
		+ DAY([TIMESTAMP])
		- (WEEKDAY([TIMESTAMP]) - 1)		
		) / 7
	)
)

Proof of Concept



Reference:


https://en.wikipedia.org/wiki/ISO_week_date

3 Likes

Wonderful! Thank you :pray:t3:
I knew there was an alternative for this problem! :slightly_smiling_face:

2 Likes

Yoı’re welcome

@Alejandra_Petro
And of course, there might always be a shorter version :hugs:

FLOOR(
	(10 +
	IF(
		MOD(YEAR([DATE]), 4) = 0,
		INDEX({0, 31, 60, 91, 121, 152, 182, 213, 244, 274, 305, 335},MONTH([DATE])),
		INDEX({0, 31, 59, 90, 120, 151, 181, 212, 243, 273, 304, 334},MONTH([DATE]))
	) + DAY([DATE]) - (WEEKDAY([DATE]) - 1)) / 7
)
1 Like