Hi, I wonder if there is a formula to calculate number of nights between two dates with times.
For instance if I have arrival at 01/09/2022 at 22:00 and departure at 01/11/2022 at 09:00 that evidently means 2 nights in a given place.
Any help and ideas will be greatly appreciated.
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.
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.
