Help

Re: Formula Help - IF Formula to generate Date

Solved
Jump to Solution
1981 1
cancel
Showing results for 
Search instead for 
Did you mean: 
Mandy_Hutchison
6 - Interface Innovator
6 - Interface Innovator

Having trouble with an IF formula.

IF({Job Size}<=10,DATEADD{Order Start Date,7,‘days’},DATEADD{Order Start Date,14’days’})

I’m trying to generate a date based on our ‘Order Start Date’, where if the integer entered in the ‘Job Size’ field is less than, or equal to, ‘10’, it will add 7 days to the ‘Order Start Date’, and if not (specifically a larger number), it will add ‘14’ to the ‘Order Start Date’.

I’m not seeing what I’m doing wrong :woman_facepalming:

1 Solution

Accepted Solutions
kuovonne
18 - Pluto
18 - Pluto

You can also consider re-arranging the formula to make it clear that the only thing that changes is the number of days being added. Also note that you do not need a third if to test if the {Job Size} is over 20. You can put the final value as the last parameter of the last IF.

DATEADD(
  {Order Start Date}, 
  IF({Job Size} < 10, 7,
    IF({Job Size} < 20, 14, 28)
  ),
  'days'
)

See Solution in Thread

9 Replies 9

You have your brackets in the wrong places, and you are missing a comma along with some parentheses.

Brackets only go around field names, and then parentheses are used for the function calls themselves.

It should look like this:

IF({Job Size}<=10,DATEADD({Order Start Date},7,'days'),DATEADD({Order Start Date},14,'days'))

However, it might make it easier to visualize your formula like this, which you can copy & paste into Airtable’s formula field and it will retain the line breaks (if you’re using Chrome):

IF(
{Job Size}<=10,
DATEADD({Order Start Date},7,'days'),
DATEADD({Order Start Date},14,'days')
)

Hope this helps! :slightly_smiling_face:

Mandy_Hutchison
6 - Interface Innovator
6 - Interface Innovator

Thank you!! That was exactly the problem. Do you know how I would add additional IF’s to this, so I can have it generate different dates for different ranges.

Here’s what I’m trying to do now, and it’s not working:

IF(
{Job Size} <= ‘10’,
DATEADD({Order Start Date}, 7, ‘days’),
IF(
{Job Size} < 30,
DATEADD({Order Start Date}, 14, ‘days’),
IF({Job Size} => 31,
DATEADD({Order Start Date}, 28, ‘days’)))

Basically, same thing as above (adding 7 days if {Job Size} is less than, or equal to, ‘10’), but now adding other outputs if {Job Size} is between ‘10’ & ‘20’ (output a date 14 days after {Order Start Date}), or if {Job Size} is ‘20’ or more, output a date that is 28 days after {Order Start Date}.

Does any of that make sense? lol

BTW, I realize some of the numbers in the formula I shared just now don’t match the numbers in my explanation, but the general idea was more of what I was trying to explain.

You’re welcome! Yes, your formula looks right, except:

  1. => is not a valid operator
    and
  2. You will need to add an extra parenthesis at the end.

Also, all quotation marks need to be straight quotation marks instead of curly quotation marks. This forum changes straight quotation marks to curly quotes unless you wrap them in code formatting, so your quotes might already be straight in your system.

kuovonne
18 - Pluto
18 - Pluto

You can also consider re-arranging the formula to make it clear that the only thing that changes is the number of days being added. Also note that you do not need a third if to test if the {Job Size} is over 20. You can put the final value as the last parameter of the last IF.

DATEADD(
  {Order Start Date}, 
  IF({Job Size} < 10, 7,
    IF({Job Size} < 20, 14, 28)
  ),
  'days'
)

Thank you so much for your help!!

Thank you!!! That works great!!!

Mandy_Hutchison
6 - Interface Innovator
6 - Interface Innovator

@ScottWorld @kuovonne Sorry that it won’t let me count both of your’s as the solutions, they both helped solve my problem!!

I already put the last parameter in the formula that I posted. In the last nested if, for a {Job Size} that is greater than or equal to 20, the formula will add 28 days. Notice that my formula has only two IFs instead of three.

I saw that after I asked :woman_facepalming: I’m new to this :joy: