Calculating the "Week of"

1723 4
Showing results for 
Search instead for 
Did you mean: 
5 - Automation Enthusiast
5 - Automation Enthusiast

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?

4 Replies 4
10 - Mercury
10 - Mercury

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


Good luck.

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:

      '6 '&
              'W GGGG'
      'E W GGGG'

Works fine for me. Here are some examples.


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!