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

Hi @LeventK @Steve ,

There is something very odd going on with the expression I wrote:

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

For some reason, when evaluating weekday 6, the expression is returning different results on the G sheet and the App-table data; please refer to the screenshots below:

all good here :+1::

different results here :-1::

image

Do you know what could be the problem?

I appreciate your help :slight_smile:

Thanks!

Your expression, reformatted:

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

Without the extraneous parentheses:

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

WEEKDAY([Date] - 1) produces a Number.

WEEKDAY([Date] - 1) / 7, a Number divided by a Number, produces a Number.

WEEKDAY([Date] - 1) / 7 + 1 produces a Number.

WEEKNUM([Date]) - (WEEKDAY([Date] - 1) / 7 + 1) produces a Number.

Wrapping that with CEILING() has no effect.

1 Like

Hi Steve,

Thanks for replying.

I read the post you suggested; I applied what I understood into the expression, I even tried changing the column type to Decimal but I’m still getting the same result :frowning:

I tried this same expression on a G Sheet, and the result is the expected, so I am confused,

image

Please help :frowning:

1 Like