- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Mar 24, 2021 03:28 PM
Regarding property bookings. We have a check-in & check-out field, both with time fields included.
Typical check-in starts at 3:00pm and check-out is 11:00am.
DATETIME_DIFF to see booking length in ‘Days’ shows 1 less that, in these cases (clearly it is counting days in 24 hour blocks, and 3:00pm-11:00am is < 24 hours).
Is there an easy workaround I’m missing, to use date fields w/ time fields included, yet calculate booking length in ONLY # days?
Let me know if this makes sense.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Mar 24, 2021 04:31 PM
Welcome to the community, @Rob_Crawford! :grinning_face_with_big_eyes: This can be done by forcing the check in and check out dates to effectively crop out the time, then taking the diff from those. In my test, I made a pair of formula fields. Here’s the one that strips out the time from the {Check In}
field:
IF({Check In}, DATETIME_PARSE(DATETIME_FORMAT({Check In}, "L"), "L"))
This formats the date and then parses the result using the same date-only pattern. Technically this doesn’t really remove the date because the date is still stored internally. It just resets it to midnight on both days, which effectively gives DATETIME_DIFF()
an apples-to-apples comparison. Just replace “Check In” with “Check Out” to get the other formula.
If you would prefer everything in a single formula, here you go:
IF(
AND({Check In}, {Check Out}),
DATETIME_DIFF(
DATETIME_PARSE(DATETIME_FORMAT({Check Out}, "L"), "L"),
DATETIME_PARSE(DATETIME_FORMAT({Check In}, "L"), "L"),
"days"
)
)
