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

Solved Solved
1 27 1,420
1 ACCEPTED SOLUTION

@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

View solution in original post

27 REPLIES 27

LeventK
Participant V

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.

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.

Steve
Participant V

Expression:
3X_7_d_7d3610b03cde5185a55f002867928fa34ee516d9.png

Output:
3X_4_9_49ec439600b20f164767c0edb6be40cc77391ce4.png

Definition of week number:

Escalated.

Nieko
Participant I

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

There is not.

Austin
Participant V

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

Gracias! I will try it.

Bahbus
Participant V

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)

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

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

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

@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

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

@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

Wonderful! Thank you
I knew there was an alternative for this problem!

Yoฤฑโ€™re welcome

LeventK
Participant V

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

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
)

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 :

different results here :

3X_4_0_40f775482ed196e3431dd26377a7b77aa6b99d28.png

Do you know what could be the problem?

I appreciate your help

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.

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

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

3X_0_5_059fbef194a1c5c77e3470ac0b53877c1c365696.png

Please help

Did you change the expression to divide by 7.0?

Hi Marc,

yes I did. still no change.

Are WEEKNUM() and WEEKDAY() returns consistent on both platforms?

I have done a test on both platforms (G Sheet and AppSheet). I used the same expression for the Result column, and this is the result:

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

AppSheet

3X_3_8_381d6785feb85809916cab81b526091b513c3e93.png

G Sheet

3X_c_3_c337456310193e361f788118d93782fcc5399ce8.png

Both platforms deliver a different result for day 7.

Also, when testing the expression, AppSheet shows the right result but delivers a different one (See the image below)


Very odd

Looks like a bug to me. Please contact support@appsheet.com for help with this.

Ratatosk
Participant V

Hi guys. Was this ever resolved?

@Ratatosk ,

Are you looking for this function introduced sometime later part of last year?

That might be it! Thank you @Suvrutt_Gurjar !

Top Labels in this Space