Calculating the "Week of"

[Jaco_Kruger]

I currently need to calculate, given any date, what is that week’s Saturday.

I am currently using to following formula:
IF(Start,DATETIME_PARSE( DATETIME_FORMAT(DATEADD(Start,-DATETIME_FORMAT(Start,'e')+6,'days'),'Y-M-D'))) , where Start is the date to be used as input.

The issue I have is that, while it works in the middle of the month, If I have a week that spans multiple months, this does not work. For example. inputting 1 December 2021 should yield 4 December 2021 as output, but I get 1 December 2021 back instead. If I input 8 December 2021, I get the expected 11 December 2021 as output.

Any ideas on how this can be fixed?

[augmented]

Hi Jaco. You might want to format this differently but it should work if I’ve understood you correctly.

DATEADD({Start},6-WEEKDAY({Start}),'days')

Good luck.

[Jaco_Kruger]

Thank you augmented, I messed around with a lot of different formulas. One trick is also to have it work around the beginning or end of the month, where your formula unfortunately breaks.

I have finally gotten something to work:

IF({ETC-Start},
    DATETIME_PARSE(
      '6 '&
          DATETIME_FORMAT(
              DATEADD(
                  {ETC-Start},
                  1,
                  'd'
                  ),
              'W GGGG'
              ),
      'E W GGGG'
      ),
	StartWk)

[augmented]

Works fine for me. Here are some examples.

[Jaco_Kruger]

Right you are!

I must have hit some strangeness or typed it wrong when I tried it. Re-doing it now worked for me as well. Your formula is way more elegant!