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
- Other questions
- Re: Calculation difference between dates ignoring ...

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
2536
0

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

Jun 12, 2022 11:04 AM

Good afternoon, I am very new to Airtable.

You will realize it, if you read my post ! :slightly_smiling_face:

I have a table with the dates and times of arrivals and departures of guests at the airport.

I want to know the number of days I will have to allocate the cars that will transport them.

The cars are rented on a full day basis.

I am trying to calculate the number of days for the cars, taking into consideration the dates of arrivals and departures of the guests.

I am using the formula DATETIME_DIFF({Date departure},{Date arrival},‘d’)+1

The formula is returning the result taking also into account the time of the day of the arrival and departure, and I really want only the full day.

I believe that the solution is very simple, but someone can help.

For example, one guest arrives on 26/06 at 11:00am and departs on 02/07 at 09:00am. the result that I’m trying to obtain is 7 days, and not 6 days

Many thanks in advance…

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

Jun 12, 2022 05:47 PM

Welcome to the Airtable community!

To ensure that the time does not influence the calculation you can use a combination of `DATETIME_PARSE`

and `DATETIME_FORMAT`

Try this formula:

```
IF(
AND({Date departure}, {Date arrival}),
DATETIME_DIFF(
DATETIME_PARSE(
DATETIME_FORMAT({Date arrival}, "YYYY-MM-DD"),
"YYYY-MM-DD"
),
DATETIME_PARSE(
DATETIME_FORMAT({Date departure}, "YYYY-MM-DD"),
"YYYY-MM-DD"
),
'days'
) + 1
)
```

Note that you might still have issues due to timezones. In this case, I recommend using SET_TIMEZONE

```
IF(
AND({Date departure}, {Date arrival}),
DATETIME_DIFF(
DATETIME_PARSE(
DATETIME_FORMAT(
SET_TIMEZONE({Date arrival}, "Europe/Lisbon"),
"YYYY-MM-DD"
),
"YYYY-MM-DD"
),
DATETIME_PARSE(
DATETIME_FORMAT(
SET_TIMEZONE({Date departure}, "Europe/Lisbon"),
"YYYY-MM-DD"
),
"YYYY-MM-DD"
),
'days'
) + 1
)
```

Note that this will only affect the formatting of the number. If you use the number in a different formula, the decimal value will be used. Also, the formatting will round the number, and some values will round down instead of up.

Note that this will still have issues if the time is less than a full hour. For example, if the duration is 24 and a quarter hours, you will get one day instead of two.

Reply

5 Replies 5

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

Jun 12, 2022 01:21 PM

Hi and welcome @TF_UNOC!

You’re on the right track. The issue is that DATETIME_DIFF() formula returns **whole integers** for any unit specifier, so simply trying to use a round up or similar won’t work.

What we can do is use the time difference in a calculation, something that does return decimals, and then format our output to integer.

So if we instead of days, ask the formula to calculate the hour difference, add 24 hours (instead of 1 day) and devide that by 24, we can now get outputs with decimals if we want.

```
(DATETIME_DIFF({Date departure},{Date arrival},'h')+24)/24
```

We can then switch tab to ‘Formatting’ and chose ‘Integer (2)’

As a final little thing we can add an IF statement to our formula so we don’t get all those NaN for records that might not have dates yet. Like so:

```
IF(
AND(
{Date departure},
{Date arrival}
),
(DATETIME_DIFF({Date departure},{Date arrival},'h')+24)/24
)
```

I hope that all made sense, English not being my native language and all.

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

Jun 12, 2022 01:31 PM

First of all, many thanks for your help.

it works very well wen the hour of arrival is higher than the hour of depart. in the opposite this don’t work so well ! :cry:

Solved
See Solution in Thread

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

Jun 12, 2022 05:47 PM

Welcome to the Airtable community!

To ensure that the time does not influence the calculation you can use a combination of `DATETIME_PARSE`

and `DATETIME_FORMAT`

Try this formula:

```
IF(
AND({Date departure}, {Date arrival}),
DATETIME_DIFF(
DATETIME_PARSE(
DATETIME_FORMAT({Date arrival}, "YYYY-MM-DD"),
"YYYY-MM-DD"
),
DATETIME_PARSE(
DATETIME_FORMAT({Date departure}, "YYYY-MM-DD"),
"YYYY-MM-DD"
),
'days'
) + 1
)
```

Note that you might still have issues due to timezones. In this case, I recommend using SET_TIMEZONE

```
IF(
AND({Date departure}, {Date arrival}),
DATETIME_DIFF(
DATETIME_PARSE(
DATETIME_FORMAT(
SET_TIMEZONE({Date arrival}, "Europe/Lisbon"),
"YYYY-MM-DD"
),
"YYYY-MM-DD"
),
DATETIME_PARSE(
DATETIME_FORMAT(
SET_TIMEZONE({Date departure}, "Europe/Lisbon"),
"YYYY-MM-DD"
),
"YYYY-MM-DD"
),
'days'
) + 1
)
```

Note that this will only affect the formatting of the number. If you use the number in a different formula, the decimal value will be used. Also, the formatting will round the number, and some values will round down instead of up.

Note that this will still have issues if the time is less than a full hour. For example, if the duration is 24 and a quarter hours, you will get one day instead of two.

Reply

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

Jun 13, 2022 01:53 AM

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

Sep 24, 2023 05:21 AM

@kuovonne You're a hero! Thanks!