Skip to main content

return the Monday of the record's week

  • June 21, 2023
  • 4 replies
  • 75 views
K
Forum|alt.badge.img+3
  • kts
  • Participating Frequently

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!

4 replies

S
Forum|alt.badge.img+21
  • Sho
  • Inspiring
  • June 21, 2023

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

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

 

K
Forum|alt.badge.img+3
  • kts
  • Author
  • Participating Frequently
  • June 22, 2023

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!

S
Forum|alt.badge.img+21
  • Sho
  • Inspiring
  • June 22, 2023

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!

A
Forum|alt.badge.img+2
  • airtableguru22
  • New Participant
  • January 7, 2025

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')



Terms & ConditionsAccessibility statement