Date Set 1
Time IN - 5/19/2024, 16:00:00 (A3)
Time OUT - 5/20/2024, 00:30:00 (B3)
Duration: 8.50 (Cell C3)
Formula = (B3 - A3)*24
Date Set 2
Time IN - 5/29/2024 15:30:00 (A4)
Time OUT - 5/30/2024 0:00:00 (B4)
Duration: 8.50 (Cell C4)
Formula = (B4 - A4)*24
FLOOR(C3-8,0.5) = 0.5
FLOOR(C4-8,0.5) = 0.0
Why is the FLOOR function giving me different answers? I'm trying to calculate the overtime hours an employee worked for specific days.
I tried to look for similar problems, but didn't find an answer.
The floor() function always rounds numbers down, so you may end up with an unexpected result when a floating point number is marginally below some value.
To avoid the issue, use mround(), like this:
=mround(C3 / 24 - "8:00", "0:30")
To simplify the whole thing, omit the factor 24 to use proper durations instead of "digital hours", like this:
| row | A | B | C | D |
|---|---|---|---|---|
| 3 | 5/19/2024 16:00:00 | 5/20/2024 0:30:00 | 8:30:00 =B3 - A3 |
0:30:00 =mround(C3 - "8:00", "0:30") |
| 4 | 5/29/2024 15:30:00 | 5/30/2024 0:00:00 | 8:30:00 =B4 - A4 |
0:30:00 =mround(C4 - "8:00", "0:30") |
Format columns C:D as Format > Number > Duration.