How to write complex formulas more easily
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.
