LinkedIn
Twitter
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:
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 
@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
@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:
@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
)
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 
:
Do you know what could be the problem?
I appreciate your help 
Thanks!
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,
Please help
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
G Sheet
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 
