Skip to main content

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!

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

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

 

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

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

 


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

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!

After some playing around with formulas, this worked for me, just replace PostDate with your date field in question to return the Monday.


DATETIME_FORMAT(DATEADD({PostDate},(0-(WEEKDAY({PostDate})
-1)),'day'),'M/DD/YYYY')



Reply


Terms & Conditions