return the Monday of the record's week

[kts]

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!

[Sho]

It seemed like an interesting problem, and I tried it!
How about this.

DATEADD(Date,8-WEEKDAY(Date),'day')

[kts]

Hmm, that seems to only return the next Sunday. 🤔 I agree, it's a tricky problem!

[Sho]

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!