Calculating Night Shift Allowances in the Staff Sc...

[E_K]

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.

[Databaser]

Hi @E_K

This should work:

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

[E_K]

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.

[Databaser]

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

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

[E_K]

Databaser:

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.

[Databaser]

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]

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.

[Databaser]

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]

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