Skip to main content

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.

Maybe the ‘false’ part of the last IF is missing:

IF({Due Date} = DATEADD (TODAY(), 30, ‘days’), “This month”, “I’m missing!”)

There are several problems that I see…



  • You’re using styled quotes in your formula. Airtable formulas only work with non-styled quotes. I’m guessing you copied some/all of that formula from an online source where the quotes were styled (perhaps somewhere in this forum?). Just retype the quotes manually and you should get non-styled quotes

  • There are several places where you put a space between a function name and its opening parenthesis. This is an invalid function call. For example 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.

There are several problems that I see…



  • You’re using styled quotes in your formula. Airtable formulas only work with non-styled quotes. I’m guessing you copied some/all of that formula from an online source where the quotes were styled (perhaps somewhere in this forum?). Just retype the quotes manually and you should get non-styled quotes

  • There are several places where you put a space between a function name and its opening parenthesis. This is an invalid function call. For example 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.


Thank you so much for answering me, Justin! I didn’t know what I was doing wrong.

Thank you so much for answering me, Justin! I didn’t know what I was doing wrong.



…and Grunty 🙂 , for looking into my formula and make a suggestion (even though by no means comparable to Justin’s)


…and Grunty 🙂 , for looking into my formula and make a suggestion (even though by no means comparable to Justin’s)


Thank you so much, Grunty, for taking the time to look into my formula and making suggestions! :grinning_face_with_big_eyes: 🙂 :grinning_face_with_sweat:

You’re welcome, Mafalda fan :winking_face:

Reply


Terms & Conditions