How to write complex formulas more easily

[James-Trory]

Formulas in Airtable can be as simple or as complex as you need them to be. When writing a formula with many conditions it can get particularly difficult. You may have run into the case yourself where you have written a complex formula and somewhere in that formula a part of it isn’t working as expected. It can be hard to find that part.

For example, I had in mind an Event Planning formula that could:

• Give me the number of days, based on {Start} and {End} dates, that an event is occurring (returned as the value “x days”)
• If the event is only one day, give me the number of hours instead (returned as the value “x hours”)
• If the event is only for 1 hour, I don’t want the value to read “1 hours” - I want “1 hour”.

This is the formula I ended up writing:

IF(DATETIME_DIFF(End,Start,'days')+1 > "1",
    "📅 "&SUM(DATETIME_DIFF(End,Start,'days')+1)&' days',
IF(AND(DATETIME_DIFF(End,Start,'days')+1 = "1",DATETIME_DIFF(End,Start,'hours') = "1"),
    "🕒 "&DATETIME_DIFF(End,Start,'hours')&' hour',
IF(AND(DATETIME_DIFF(End,Start,'days')+1 = "1",DATETIME_DIFF(End,Start,'hours') != "1"),
    "🕒 "&DATETIME_DIFF(End,Start,'hours')&' hours'
)))

And here is an example of what it returns:

It’s not the most complex formula I’ve ever written, but it was tricky to test and make sure that if any of these conditions matched that the correct value was being returned by the formula.

You could definitely sit and attempt to write this whole thing out trying to find errors along the way, but there’s an easier method.

Break up the formula:

Start first by deciding what conditions you want the formula to check for, as I did above, then step-by-step create each condition as their own formulas.

I began with two fields - one that calculates the number of days and a second that calculates the number of hours:

{Days}
DATETIME_DIFF(End,Start,'days')+1

{Hours}
DATETIME_DIFF(End,Start,'hours')

So I know I’ve got my days and hours part correct. I can see that the values these are returning are correct. On to the next step.

I create a third formula that starts to combine {Days} and {Hours} depending on what matches.

{Days or Hours}

IF({Days} > 1, {Days}&’ days’,
IF(AND({Days} = 1, {Hours} != 1), {Hours}&’ hours’
))

To review: if days is greater than 1, give me “x days”, if days is equal to 1 AND hours is not equal to 1, give me “x hours”.

Now I can see that days or hours works, depending on what matches. Next step.

I want to add that if {Hours} = 1, I only want the word “hour” not “hours”. So I add that.

{Days or Hours}

IF({Days} > 1, {Days}&’ days’,
IF(AND({Days} = 1, {Hours} != 1), {Hours}&’ hours’
IF(AND({Days} = 1, {Hours} = 1, {Hours}&’ hour’
)))

So that’s my formula, basically. Now, I could leave it like this and hide the {Days} and {Hours} fields, but really I’d like to combine all of them into one single formula for the sake of efficiency.

Combing many formulas into one formula:

Simply put, I’m going to copy the {Days} formula and the {Hours} formula that I created earlier and paste them into the final formula wherever those two are referenced. The easiest way to do this is to duplicate the third formula to a fourth formula and use that to combine everything so that I still have the third formula if I screw something up.

Starting with:

IF({Days} > 1, {Days}&’ days’,

I will copy the {Days} formula and replace {Days} in my duplicated fourth formula with that formula for {Days}:

IF(DATETIME_DIFF(End,Start,‘days’)+1 > 1, DATETIME_DIFF(End,Start,‘days’)+1&’ days’)

If I review this now I’ll see I have an error where the second reference to {Days} has “+1”. That throws an error, so I wrap it in SUM():

IF(DATETIME_DIFF(End,Start,‘days’)+1 > 1, SUM(DATETIME_DIFF(End,Start,‘days’)+1)&’ days’,

I go through each condition, checking as I go that the copy/paste is working, and I end up with my final formula, adding some emojis for flavor:

IF(DATETIME_DIFF(End,Start,'days')+1 > "1",
    "📅 "&SUM(DATETIME_DIFF(End,Start,'days')+1)&' days',
IF(AND(DATETIME_DIFF(End,Start,'days')+1 = "1",DATETIME_DIFF(End,Start,'hours') = "1"),
    "🕒 "&DATETIME_DIFF(End,Start,'hours')&' hour',
IF(AND(DATETIME_DIFF(End,Start,'days')+1 = "1",DATETIME_DIFF(End,Start,'hours') != "1"),
    "🕒 "&DATETIME_DIFF(End,Start,'hours')&' hours'
)))

Having done this I can now delete {Days}, {Hours} and my third formula, leaving the fourth as my final, single formula that does everything I need. Voila!

I use this method all the time when writing complex formulas and it makes things so much easier.

[kuovonne]

Welcome to the Airtable community! It is always nice to see Airtable employees taking an interest in this community forum.

I applaud your efforts to help users write complex formulas more easily. It also looks like you are fairly new to writing formulas. As an Airtable user who has written several complex formulas, I agree that complex formulas can be difficult to write. As a former technical writer, I can also appreciate that teaching people how to do a complex process can be even harder. You are trying to do two difficult things at the same time!

Writing a formula can be a messy process, which your article points out. However, while initial formulas might have problems, final formulas should be as cleanly written as possible. Your final formula has a few issues that prevent it from being a model formula for users new to the formula writing process.

I suspect that you will discover these issues on your own as you write more formulas. On the other hand, if you would like coaching on formula writing, feel free to reach out.

[James-Trory]

I’m sincerely interested to learn how you think the formula could be improved upon. As a simple technique to combine many formulas into one, I see this as being the most simple to understand. It may not, admittedly, be the best example I could have given.

[kuovonne]

When evaluating a formula I tend to look at the following:

• syntax (does the formula run)
• logic (does the formula produce the correct result)
• style (how human-readable is the formula)

Evaluating the syntax of a formula is easy. In general, if a formula runs, the syntax is correct. Some beginning formula writers get tripped up on syntax and have trouble getting a formula to even save, especially with complex formulas. But syntax is not the issue here.

The logic of a formula is a bit harder to evaulate. In general, if the formula produces the correct output, the logic is okay. On the other hand, people often forget to test edge cases. Here are some edge cases you may not have considered.

• It looks like if an event starts and ends on different days, you want to include both the start date and end date in your count of days. For example, if an event starts on Monday and end the next day on Tuesday, you want the formula to say 2 days. However, if the event starts on Monday evening and ends on Tuesday morning, that would be less than 24 hours, and thus your formula will say the number of hours, not 2 days. You might never encounter that situation, especially since this is just a demo, but it is something to consider out in the wild.

• If either the start or end dates are missing, your formula will produce an error. Some people are okay with seeing the error, but others do not like seeing the error.

• If the end date is before the start date, your formula will not say anything, which might not be what you actually want. You might want to display a warning message instead.

Even if the logic of a formula works, there are many ways to accomplish the same output in code. That is where style comes in. Style includes which functions you choose, how you combine your functions, and how you format the formula.

Here are a few stylistic issues that relate to the functions you use and how you combine them:

• DATETIME_DIFF(End,Start,'days')+1 > "1" You are comparing a number with a string, rather than a number with a number. You do not need the quotes around "1".

• &SUM(DATETIME_DIFF(End,Start,'days')+1)& You used SUM because you were getting an error for trying to add a number to a string. Airtable will do type conversion for you when you concatenate a number and a string, but not when you add a number to a string. You can simply use parenthesis without SUM: &(DATETIME_DIFF(End,Start,'days')+1)&

• You do not take advantage of the else part of the IF function.

• Your nested IF statements could be restructured so that you do not need the AND functions.

Here are some stylistic issues that relate to formatting:

• Your do not have much white space. I recommend putting in more space between you parameters to make reading your formula easier.

• You have some very long lines. I recommend splitting up formulas into more lines to shorten the line length.

• You use a mix of single quotes and double quotes. I recommend sticking to a single quote style within the same formula, unless you have a compelling reason to use different types of quotes.

[kuovonne]

By the way, when making complex formulas, knowing how to nest functions inside each other is an essential skill. Also, the most common issues that I have when writing complex formulas are syntax issues–missing or extra parentheses or commas.