Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Airtable Community
- Discussions
- Ask A Question
- Formulas
- Re: If and other formulas combined - Need Help

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Solved

Jump to Solution

1
2769
0

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Comment Post Options

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.

1 Solution

Accepted Solutions

Solved
See Solution in Thread

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Mar 09, 2021 09:23 PM

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.

Reply

6 Replies 6

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Mar 09, 2021 07:19 PM

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

Reply

Solved
See Solution in Thread

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Mar 09, 2021 09:23 PM

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.

Reply

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Mar 10, 2021 08:18 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Mar 10, 2021 09:13 AM

Reply

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Mar 10, 2021 09:31 AM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Mar 10, 2021 03:30 PM

You’re welcome, Mafalda fan :winking_face:

Reply