Hi all, this is a problem that's been troubling me for some time.
Let's say that I have the following date, "08/18/2021". I know that it falls on a wednesday, and I want to substract enough days in order to get "08/16/2021" as the result. Same for any other date in the same week, I'd like to automatically detect how many days have passed in the week so it always results in the Monday of that week.
My approach has been the following:
a!localVariables( local!ctrlday: weekday(todate("08/18/2021")), datetext(todate("08/18/2021") - day() * (local!ctrlday - 3), "yyyy/MM/dd"),)
And it "works" for that example, but if I were to calculate "08/17/2021" and substract 2, it would end up with "08/15/2021".
Any ideas on how to make this behave properly?
Thank you in advance.
Discussion posts and replies are publicly visible
Hi Gabriel, the weekday() function is great in this scenario:
ri!date-weekday(ri!date)+2
Thanks, Chris! The weekday() function indeed is super helpful.
The issue in my code apparently was in the datetext() function. It subtracted an extra day, and I didn't know until I looked at the code with fresh eyes, and decided to use text() instead.
It ended up like this, and it works great. It always returns the Monday of the week in question
a!localVariables( local!ctrlday: todate("08/25/2021"), local!daysDiff: weekday(local!ctrlday) - 2, local!endResult: local!ctrlday - (local!daysDiff), { "Date: " & local!ctrlday & ". Weekday: " & weekday(local!ctrlday), local!daysDiff, text(local!endResult, "yyyy/MM/dd") })