Unexpected results of FLOOR function with time values

[Kobko]

I have a difficulties using the FLOOR function with time values. It mostly works, however if the first parameter is calculated, results are not always as expected.
Example:
When I round down a value "10:00" using a formula =FLOOR("10:00"-TIME(9;30;0); "0:30"), the result is "0:30" as expected. However, if I use =FLOOR("8:00"-TIME(7;30;0); "0:30"), the result is "0:00", which is not correct. I found out that it helps to change the formula to =FLOOR("8:00"; "0:30")-TIME(7;30;0), however I don't understand what is wrong with the first option. Has anybody an idea?

 

[Marius44]

Hi
Instead of FLOOR use ROUND.MULTIPLE
Example: =ROUND.MULTIPLE("08:00"-TIME(7;30;0);"0:30")

Hi,
Mario

 

[Kobko]

Hi Marius44,

I think you mean MROUND function. The function is not usefull for me as I need the time to be always rounded down to nearest 30 minutes. For example if the time is 7:50, after a subtraction of 7:30 I need the result to be 0:00 (which is 0:20 rounded down to nearest 0:30).
Except of it, I would like to understand why the FLOOR function does not work as expected sometimes.

Roman

 

[FormR]

I would like to understand why the FLOOR function does not work as expected sometimes.

Hi, the problem is almost certainly caused by "floating point rounding errors" (an internet search will reveal lots of information on the subject) - the TIME() function appears to round these errors better so you could jump through a few hoops like this to get to the right answer.

=FLOOR(LET(t,TIME(8,0,0)-"07:30",TIME(HOUR(t),MINUTE(t),SECOND(t))),"00:30")

 

[Georgiboy]

You could also use the numeric representation of '00:30' which is: 0.020833

=FLOOR("8:00"-TIME(7,30,0), 0.020833)

This is also working around the floating point issue from what i understand.

 

[FormR]

You could also use the numeric representation of '00:30' which is: 0.020833

It didn't occur to me that the rounding error could be in the significance argument. This also appears to resolve the problem.

=FLOOR("8:00"-TIME(7,30,0),ROUND("00:30",13))

Or

=FLOOR("8:00"-TIME(7,30,0),TIME(0,0,30))

Or to be even more robust

=FLOOR(ROUND(TIME(8,0,0),13)-ROUND(TIME(7,30,0),13),ROUND(TIME(0,30,0),13))

 

[Kobko]

@Georgiboy, @FormR Thank you for your hints. I spent some time playing with your suggestions. I understand, that rounding numbers can cause that some formulas work while the others do not work as expected. But therefore I don't understand why explicit rounding solves my issue.
A numerical representation of TIME(0;30;0) is 0,020833 (a periodic number). If I round the number, for example ROUND(TIME(0;30;0);13), the result will be less precise than the original representation. I think that using as much precise representation as possible in calculations should be the best. I would understand why some calculations do not work with rounded numbers, but not with more precise numbers. Am I missing something here?

@FormR Why you round the numbers to 13 digits? Is it just an experimentally determined value or it has any reason?

 

[FormR]

Why you round the numbers to 13 digits?

It's quite an arbitrary choice still, but it's the number of digits of precision that a number can be displayed at in a cell in Excel.

But therefore I don't understand why explicit rounding solves my issue.

Have a read of this and see if it helps.
Floating-point arithmetic may give inaccurate result in Excel - Microsoft 365 Apps