Calculating number of nights between two dates with times

Hi @E_K

This should work:

DATETIME_DIFF({most recent date}, {other date}, "days")+1

Thank you @Databaser, it does not calculate correctly in the following situation:
Arrival ({other date} = 01/09/2022 22:00
Departure ({most recent date}) = 01/11/22 23:00.
Your result is 3, which is not correct as only 2 nights have passed.

Hmm, weird, it jumped numbers when I changed field titles…

Use DATETIME_DIFF({Departure}, {Arrival}, "days")

Thank you again @Databaser. This formula also produces strange result:
Arrival = 01/10/2022 22:00
Departure = 01/12/2022 13:00
The result is 1, which is not true as 2 full nights should be the correct count.

God I hate working with date fields :face_without_mouth:

If you remove your time indication or work with 2 formula fields that only uses the dates, it should work correctly. Airtable stores all dates and times in GMT, which causes these kind of problems.

Indeed, date fields are tricky.
I can not remove time because that’s exactly what I need to calculate - number of nights between two given dates with times - I know arrival date/time and departure date/time and need to find out how many nights passed.
It does not actually matter whether time is GMT or local because both fields are in the same time zone.

You could create 2 extra formula fields that take the date with time to display them as date with no time and then use a formule field to calculate the number of nights.

Hey,

A bit late but if you format your dates (with time) in your datetime-diff formula, it works: