How to best deal with rounding errors when comparing dates/times

Question

I have a column that contains a series of date/time values . I am wanting to determine how many of the time slots fall between 10:00 and 11:00 am, 11:00 am and noon, etc. To pull the time out of my date/time entry I am using the formula "=A1 - int(A1)", where A1 is the date/time. However, this is giving me weird rounding errors at the 10th or so decimal place. This error in rounding pulls the extracted time value above or below the "expected" value for a given time, so I hesitate to simply round my values to a given decimal place. How do folks generally work around this?

To give an example, 8 pm is 0.83333333333333. Using the "int" formula to pull the time from my given date, I get 0.833333333328483, making that particular entry be listed as happening between 7 and 8 as opposed to between 8 and 9.

Answer 1

You can get the time portion of a date time value with the timevalue() function. It does not suffer from the kind of rounding errors that are unavoidable when subtracting one number from another under the IEEE 754 standard used by all major spreadsheet software.

Another easy way to compare the time portion of a date time is to use text(), like this:

=text(A1, "hh:mm:ss") = text(A2, "hh:mm:ss")

See Working with date and time values in Google Sheets.