Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Jun 21, 2023 02:11 PM
I'm trying to set up a formula that will give me the date of "Monday" relative to a given record's date. So if a record was dated today Wed June 21, I'd want the formula to give me Mon June 19.
I've done some searching on the forum here and found this formula:
DATEADD(Date,1-DATETIME_FORMAT(Date,'E'),'day')
But I'm having some issues with it—it's returning the date of Sunday, not Monday, and I can't for the life of me figure out how to adjust it!
Does anybody know how I might fix this? Thank you!
Jun 21, 2023 04:51 PM
It seemed like an interesting problem, and I tried it!
How about this.
DATEADD(Date,8-WEEKDAY(Date),'day')
Jun 22, 2023 11:45 AM
Hmm, that seems to only return the next Sunday. 🤔 I agree, it's a tricky problem!
Jun 22, 2023 04:32 PM
I was wrong about before and after the week😹
How about this.
DATEADD(Date,WEEKDAY(Date)*-1-6,'day')
Note that different Timezone between fields will cause time differences!