Help

Re: Calculating number of nights between two dates with times

1955 1
cancel
Showing results for 
Search instead for 
Did you mean: 
E_K
6 - Interface Innovator
6 - Interface Innovator

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.

8 Replies 8

Hi @E_K

This should work:

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

E_K
6 - Interface Innovator
6 - Interface Innovator

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

E_K
6 - Interface Innovator
6 - Interface Innovator

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.

E_K
6 - Interface Innovator
6 - Interface Innovator

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.

Chartie
4 - Data Explorer
4 - Data Explorer

Hey,

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

DATETIME_DIFF(DATETIME_FORMAT({End Date},"MM-DD-YYYY"),DATETIME_FORMAT({Begin Date},"MM-DD-YYYY"),"days")
 
NB: needs to be US date format