Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Airtable Community
- Discussions
- Ask A Question
- Formulas
- Re: If...else and DATETIME_DIFF

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Solved

Jump to Solution

0
1864
2

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Comment Post Options

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Oct 06, 2021 10:07 PM

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.

Solved! Go to Solution.

1 Solution

Accepted Solutions

Solved
See Solution in Thread

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Oct 07, 2021 06:40 AM

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())
)
```

Reply

6 Replies 6

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Oct 07, 2021 12:06 AM

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

Reply

Solved
See Solution in Thread

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Oct 07, 2021 06:40 AM

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())
)
```

Reply

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Oct 07, 2021 09:17 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Oct 07, 2021 09:17 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Oct 07, 2021 10:51 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Oct 08, 2021 05:18 AM