Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Jun 23, 2021 08:54 AM
Hi everyone -
I’m still very new to Airtable and ran into a formula error that I’m trying to solve.
Ideally, we would like to group tasks by the week they are due. I was able to find the formula to output the week number but can’t seem to find one that works that will produce the following output: “Week of 06/21”.
I did find the following two formulas from a previous post: Display the "week of" for a given date starting with Monday
CONCATENATE(“Week of”," ",DATETIME_FORMAT(DATEADD({Date},-DATETIME_FORMAT({Date},‘e’),‘days’),‘M/D’))
DATETIME_FORMAT({Date},‘e’)+1,‘days’),‘M/D’))
However, when I go to add the formula to my calculated field it returns an error. Could it be because my {Due Date} field is calculated as well?
This is the formula that I am using: CONCATENATE(“Week of”," ",DATETIME_FORMAT(DATEADD({Due Date},-DATETIME_FORMAT({Due Date},‘e’)+1,‘days’),‘M/D’))
The specific error is as follows: Sorry, there was a problem saving this field. Invalid formula. Please check your formula text.
Thanks so much for your help!
Heather
Solved! Go to Solution.
Jun 23, 2021 10:49 AM
The second formula, which is a piece of the first, has one complete function and is trailed by incomplete pieces from two other functions.
The first formula produces no errors if you replace the curly quotes with straight ones:
CONCATENATE("Week of ", DATETIME_FORMAT(DATEADD({Date},-DATETIME_FORMAT({Date},"e"),"days"),"M/D"))
This forum adds “curly” quotes by default. When typing here, wrap formulas in backticks (`) to prevent it.
Jun 23, 2021 10:49 AM
The second formula, which is a piece of the first, has one complete function and is trailed by incomplete pieces from two other functions.
The first formula produces no errors if you replace the curly quotes with straight ones:
CONCATENATE("Week of ", DATETIME_FORMAT(DATEADD({Date},-DATETIME_FORMAT({Date},"e"),"days"),"M/D"))
This forum adds “curly” quotes by default. When typing here, wrap formulas in backticks (`) to prevent it.
Jun 23, 2021 02:06 PM
Thanks @Kamille_Parks! Worked like a dream.