Calculating the "Week of"

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?

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.

2 Likes

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)

Works fine for me. Here are some examples.

image

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!

1 Like

This topic was solved and automatically closed 15 days after the last reply. New replies are no longer allowed.