DATETIME_DIFF w/ time field ... can I show strictly # of DAYS between dates, even if less than 24 hours?

[Rob_Crawford]

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.

[Justin_Barrett]

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