Help

return the Monday of the record's week

Topic Labels: Dates & Timezones Formulas
1335 3
cancel
Showing results for 
Search instead for 
Did you mean: 
kts
5 - Automation Enthusiast
5 - Automation Enthusiast

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!

3 Replies 3
Sho
11 - Venus
11 - Venus

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

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

 

kts
5 - Automation Enthusiast
5 - Automation Enthusiast

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

Sho
11 - Venus
11 - Venus

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!