Skip to main content

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.

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.


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)

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.


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!

Reply


Terms & Conditions