Help

Formula to Display "Week Of" Date

Topic Labels: Dates & Timezones Formulas
Solved
Jump to Solution
7354 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.