Help

The Community will be temporarily unavailable starting on Friday February 28. We’ll be back as soon as we can! To learn more, check out our Announcements blog post.

Formula to Display "Week Of" Date

Topic Labels: Dates & Timezones Formulas
Solved
Jump to Solution
8427 4
cancel
Showing results for 
Search instead for 
Did you mean: 
shirt26
4 - Data Explorer
4 - Data Explorer

Hi AT Community,

 

I'm new to Airtable and ’m trying to create a formula that would display the Monday of the week / "week of" date for a given date. Monday is the start date of the week. 

I've tried formulas from other posts but when I add the formula to my field, it gives an error.

The formulas I've tried are:

DATETIME_FORMAT({Date},‘e’)+1,‘days’),‘M/D’))

--

 

 

 

CONCATENATE("Week of ", DATETIME_FORMAT(DATEADD({Date},-DATETIME_FORMAT({Date},"e"),"days"),"M/D"))

 

 

 

 --

DATETIME_FORMAT(IF(WEEKDAY({Date Due})>1,DATEADD({Date Due},-WEEKDAY({Date Due})+1,'days'),IF(WEEKDAY({Date Due})<1,DATEADD({Date Due},-WEEKDAY({Date Due})-6,'days'),{Date Due})),'l')

I really just want to get the Monday of the week when the record is created. So if the record was created on, say, January 19, 2023 - the Monday of that week would be January 16, 2023. And the field would display exactly "January 16, 2023."

Thank you in advance! 🙏

1 Solution

Accepted Solutions
jasonsamuels
5 - Automation Enthusiast
5 - Automation Enthusiast

Try this:

"Week of " & DATETIME_FORMAT(DATEADD(Date,1-DATETIME_FORMAT(Date,'E'),'day'),'ll')

See Solution in Thread

4 Replies 4
jasonsamuels
5 - Automation Enthusiast
5 - Automation Enthusiast

Try this:

"Week of " & DATETIME_FORMAT(DATEADD(Date,1-DATETIME_FORMAT(Date,'E'),'day'),'ll')

Thank you so much! Worked like a dream! 🙂

No this is old but hoping someone can help...I love this but now when I group by this field it doesn't sort by date but by a,b,c order. is there a workaround for this?

I solved for the alphabetical sort by adding WEEKNUM(date) to the beginning of the string, which using this week for example renders as 48 - Week of November 27, 2023.