Solved

Calculation difference between dates ignoring the time of day

Forum|Forum|3 years ago
June 12, 2022
5 replies
141 views

TF_UNOC
New Participant

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…

Best answer by kuovonne

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:

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.

+14

momentsgoneby80
Inspiring
Forum|Forum|3 years ago
June 12, 2022

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.

TF_UNOC
Author
New Participant
Forum|Forum|3 years ago
June 12, 2022

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.

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:

+29

kuovonne
Brainy
Answer
Forum|Forum|3 years ago
June 13, 2022

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:

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 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.

TF_UNOC
Author
New Participant
Forum|Forum|3 years ago
June 13, 2022

Dear Kuovonne and momentsgoneby80. It Works very well :ok_hand: . Many thanks for your Help :partying_face: .

globers
Inspiring
Forum|Forum|2 years ago
September 24, 2023

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 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.

@kuovonne You're a hero! Thanks!

Sign up

Login to the community

Scanning file for viruses.

This file cannot be downloaded