Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Dec 08, 2021 11:18 AM
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?
Dec 08, 2021 12:16 PM
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.
Dec 22, 2021 11:25 AM
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)
Dec 22, 2021 12:49 PM
Works fine for me. Here are some examples.
Dec 22, 2021 01:22 PM
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!