Welcome to the Airtable Community! If you're new here, check out our Getting Started area to get the most out of your community experience.

# If...else and DATETIME_DIFF

Topic Labels: Formulas
Solved
Jump to Solution
568 6
cancel
Showing results for
Search instead for
Did you mean:
4 - Data Explorer

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.

1 Solution

Accepted Solutions
18 - Pluto

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())
)
``````
6 Replies 6
8 - Airtable Astronomer

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”))

18 - Pluto

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())
)
``````
4 - Data Explorer

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!

4 - Data Explorer

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.

8 - Airtable Astronomer

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.

18 - Pluto

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.