Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Mar 09, 2021 06:05 PM
Hi, community! It`s my first time writing a formula and I need your help!
I’d like to create a formula to represent a due date ocurring ‘today’, a due data ocurring ‘this week’ and a due date ocurring this month.
I tried to create that formula combining an IF function and a DATEADD function.
Airtable keeps informing the formula is invalid.
What’s is the correct way to write that?
I thought the following formula was ok, but Airtable says it’s wrong
IF({Due Date} = TODAY (), “Today”,
IF ({Due Date} = DATEADD (TODAY() , 7, ‘days’), “This week”,
IF({Due Date} = DATEADD (TODAY(), 30, ‘days’), “This month” ) ) )
The formula is wrong and I don’t know how to fix it. I tried several variations and all of them are wrong as well.
Could you please help me solve this?
P.S: The table is written in Portuguese. I translated some fields into English to help your analysis.
Solved! Go to Solution.
Mar 09, 2021 09:23 PM
There are several problems that I see…
TODAY()
is correct, but TODAY ()
is not.On a side note, the logic of your formula is a little off in the “This week” and “This month” sections. You’re comparing the due date against a date modified from today using the =
operator, which means that only a date exactly matching that modified date—either 7 or 30 days out, depending on the section—will create the desired output. What you should be doing is checking to see if the due date is less than (i.e. before) the modified date in each case. That will apply the desired label across a span of days, not just on a single day.
Here’s your formula with all of those changes applied:
IF({Due Date} = TODAY(), "Today",
IF({Due Date} < DATEADD(TODAY(), 7, "days"), "This week",
IF({Due Date} < DATEADD(TODAY(), 30, "days"), "This month")))
Also be aware that TODAY()
is calculated based on GMT, not your local timezone. Depending on where you live, you might see items match “Today” during a certain part of the day, but then change to having no label later the same day. That change will happen when GMT rolls over to the next day. There are ways to work around this, but it makes the formula more complicated. If you want help setting that up, just holler.
The false argument in the “if” function is optional. The docs don’t mention this, sadly. If omitted, the output is automatically blank.
Mar 09, 2021 07:19 PM
Maybe the ‘false’ part of the last IF is missing:
IF({Due Date} = DATEADD (TODAY(), 30, ‘days’), “This month”, “I’m missing!”)
Mar 09, 2021 09:23 PM
There are several problems that I see…
TODAY()
is correct, but TODAY ()
is not.On a side note, the logic of your formula is a little off in the “This week” and “This month” sections. You’re comparing the due date against a date modified from today using the =
operator, which means that only a date exactly matching that modified date—either 7 or 30 days out, depending on the section—will create the desired output. What you should be doing is checking to see if the due date is less than (i.e. before) the modified date in each case. That will apply the desired label across a span of days, not just on a single day.
Here’s your formula with all of those changes applied:
IF({Due Date} = TODAY(), "Today",
IF({Due Date} < DATEADD(TODAY(), 7, "days"), "This week",
IF({Due Date} < DATEADD(TODAY(), 30, "days"), "This month")))
Also be aware that TODAY()
is calculated based on GMT, not your local timezone. Depending on where you live, you might see items match “Today” during a certain part of the day, but then change to having no label later the same day. That change will happen when GMT rolls over to the next day. There are ways to work around this, but it makes the formula more complicated. If you want help setting that up, just holler.
The false argument in the “if” function is optional. The docs don’t mention this, sadly. If omitted, the output is automatically blank.
Mar 10, 2021 08:18 AM
Thank you so much for answering me, Justin! I didn’t know what I was doing wrong.
Mar 10, 2021 09:13 AM
…and Grunty :slightly_smiling_face: , for looking into my formula and make a suggestion (even though by no means comparable to Justin’s)
Mar 10, 2021 09:31 AM
Thank you so much, Grunty, for taking the time to look into my formula and making suggestions! :grinning_face_with_big_eyes: :slightly_smiling_face: :grinning_face_with_sweat:
Mar 10, 2021 03:30 PM
You’re welcome, Mafalda fan :winking_face: