If...else and DATETIME_DIFF

[jy22]

Hey folks,

I’m trying to calculate the number of days someone has worked in a job. I’m familiar with the DATETIME_DIFF() function, which works great when you have a Start Date and an End Date, but what if you don’t have an end date?

In other words, imagine that someone is currently in their position, therefore the field registering their end date is empty.

The theoretical solution I came up with is creating another field with the formula TODAY(), and then, if the field for end date is empty, use the field for today to calculate the difference between today and the start date, and calculate the time that the person has worked.

I’m having trouble with the syntax! Any thoughts be greatly appreciated.

[JonathanB]

You will need the formula to say, “If no end date, then the difference between start date and today, otherwise the difference between start date and end date”. You don’t need a separate field with NOW() in it because you can just incorporate this into the formula, i.e.

IF({End date}="", DATETIME_DIFF(NOW(), {Start date}, “days”), DATETIME_DIFF({End date}, {Start date}, “days”))

[kuovonne · Accepted solution]

When nesting functions inside each other, I like a multi-line syntax.
In order to type a multi-line syntax you need to use an external editor, and then paste in the formula into the formula box.

DATETIME_DIFF(
    IF({End Date}, {End Date}, TODAY()),
    {Start Date},
    "days"
)

Note that this will count every day. TODAY() is also based on midnight GMT time, so you may get results that are off by one day, depending on your time zone and the time the formula is calculated. You can use NOW(), but that is much more resource intensive and can potentially slow down your base.

If you want to exclude weekends, you can also use WORKDAY_DIFF

WORKDAY_DIFF(
    {Start Date},
    IF({End Date}, {End Date}, TODAY())
)

[jy22]

Awesome, thank you so much, and great tip about using an external editor to handle syntax. It’s super annoying trying to write formulas inside Airtable!

[jy22]

Thank you, this makes a lot of sense but I’m not understanding the syntax. I kept getting an error when I tried to use your formula. Is there a parentheses missing somewhere? Not sure.

[JonathanB]

Could be the quotation marks. I just noticed some of them were different in the formula I wrote above. I have amended now. (For some reason Airtable doesn’t recognise the ‘curly’ quotation marks - I don’t know what they’re called!) It might be how your browser is displaying them.

[kuovonne]

It’s the curly quotes versus straight quotes. Airtable formulas don’t like curly quotes. Unfortunately, the forum software often converts straight quotes to curly quotes unless you format the text as code.