Skip to main content

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.

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"


    )


)

Reply


Terms & Conditions