I've made a form which from 'today's date', calculates the previous week's Monday and Sunday. I can get the form to work fine on my computer, but on some (not all) other people's computers the date calculation on a Monday brings up the Monday two weeks ago. That's using the 'date calculation' number 1 below.
We then tried the 'date calculation' number 2 below, and this works on some computers, but gives the previous week's Sunday and Saturday (i.e. one day further back) on other computers.
We then tried the 'date calculation' number 3 below, and this again works on some computers, but not on all. When it doesn't work, it gives the previous week's Sunday and Saturday (i.e. one day further back).
It seems to be a rounding issue, with some computers rounding but not all. We've tried several alternatives for the 'date calculation' row, but each time it seems to work ok on one computer but not on another.
It's always the Sunday/Monday which is causing the problem. I'd be very grateful if anyone can suggest a work around! I'm uploading a copy of the original form, and an alternative form we tried with round().
today()-(((decimal-date-time(today()) -4) mod 7) +7)
today()-(((decimal-date-time(today()) -3) mod 7) +6)
today()-(((decimal-date-time(today()) -3.9) mod 7) +6)
date(int(decimal-date-time(${sel_date}))-((int(decimal-date-time(${sel_date}))+4) mod 7)-6)
Lucie, I just wanted to check if we're on the same page. For me, Monday marks the start of the week - does that align with how you see it? For example, I consider Feb 10, 2025 (Monday) to Feb 16, 2025 (Sunday) as a one full week, meaning all dates within this range (10 and 16 included) should return Feb 3, 2025 as the answer. However, if you consider Sunday as the first day, your week would run from Feb 9, 2025 (Sunday) to Feb 15, 2025 (Saturday) (9 and 15 included), which could shift the results for Sundays!
The calculation I shared assumes Monday as the First Day, please feel free to replace ${sel_date} with today().
Hi MinimalPotato,
Thank you for looking into this!
Yep, you understood the date calculations we wanted to show. Unfortunately with that formula if I select a Sunday it gives me the Monday of the same week, unlike the other days of that week which give me the Monday of the previous week!
The good news is that imbetween time, we've found a solution. It seems that moving the brackets helps. This now works both for me and my colleague (and hopefully other computers)!
round(${date} - (round(decimal-date-time(${date}) -4) mod 7)-7)
