First weekday should be Monday

[Michael2]

WEEKDAY() begins counting from Sunday. i.e., Sunday=1, Saturday=7

My current expression [DATE]+7-WEEKDAY([DATE]) gets the weekending date of [DATE]

I need an expression that gets the weekending date with the first day of the week being Monday and the last being Sunday, Monday=1, Sunday=7

Any ideas would be appreciated.

[Phil]

See the last paragraph in topic “Function” in this article https://help.appsheet.com/expressions/expression-types/date-and-time-expressions

It says:

The value returned by WEEKNUM() assumes the week starts on Sunday and ends on Saturday. To get the week number when the week starts on Monday and ends on Sunday, use the following expression:

IF(AND(MONTH([Date]) = 1, Day([Date]) = 1), 1, WEEKNUM([Date] - 1))

[Michael2]

This is great and I thank you but, how do I now convert the returned week number to the week ending date? I really am not interested in the week number I just need the week ending date. It would be great if the appsheet weekday expression functioned like the google sheet and gave us types…

[Phil]

Sorry, I misread your post. Try:

[DateOfDay]+7-WEEKDAY([DateOfDay]-1)

[Michael2 · Accepted solution]

Thank you Phil & Steve

Phil:

[DateOfDay]+7-WEEKDAY([DateOfDay]-1)

Steve:

[DATE]+7-WEEKDAY([DATE])-1

@Steve: The expression is the same as Phil’s isn’t it? Phil just wrote the fields differently [DayOfDate] and you didn’t [DATE].

[Steve]

Would this not work?

[DATE]+7-WEEKDAY([DATE])-1

[Phil]

Yes, I wrote a simple test app and called my field [DateOfDay].
I wanted to try all of the edge cases. Steve’s formula is better because it uses your field name.