Upcoming database upgrades. Airtable functionality will be reduced for ~15 minutes at 06:00 UTC on Feb. 4 / 10:00 pm PT on Feb. 3. Learn more here
Aug 27, 2020 09:53 AM
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:
Solved! Go to Solution.
Aug 27, 2020 07:38 PM
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'
)
Aug 27, 2020 10:24 AM
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:
Aug 27, 2020 11:26 AM
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.
Aug 27, 2020 03:55 PM
You’re welcome! Yes, your formula looks right, except:
=>
is not a valid operatorAlso, 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.
Aug 27, 2020 07:38 PM
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'
)
Aug 28, 2020 06:18 AM
Thank you so much for your help!!
Aug 28, 2020 06:19 AM
Thank you!!! That works great!!!
Aug 28, 2020 06:40 AM
@ScottWorld @kuovonne Sorry that it won’t let me count both of your’s as the solutions, they both helped solve my problem!!
Aug 28, 2020 07:00 AM
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 IF
s instead of three.
Aug 29, 2020 07:37 AM
I saw that after I asked :woman_facepalming: I’m new to this :joy: