Do you want to round the working time to the nearest 15 minutes? I will show you how to do it.
We can round our working time to the nearest multiple of 15 minutes. In most cases, when we round in Excel and reach the middle point, we start to round up. In the case of 15 minutes, the middle point is 7.5 minute. Now, let’s start the rounding. It’s a simple task. We can just use the MROUND function, write the number, which in our case will be the time, and then write the multiple as time, i.e. in double quotes. In this case, the first two digits correspond to hours, and the last two ones correspond to minutes. If we need seconds, we just write them further (Fig. 1)
=MROUND(A2,“00:15:00”)
Now, let’s check our time. In the first three examples, we didn’t reach the middle point, so we go down. However, after we pass the middle point, which is shown as a threshold, we start rounding up. In the last but one example, we have exactly the same value, so we leave it just as it is. However, in the very last example, we didn’t reach the next middle point, so we still round down (Fig. 2)
Sometimes, we always want to round down. In this case, we can use the FLOOR function. In the first argument, we have to write the time. In the second one, which is called significance, we write the same multiple as in the MROUND function, which is time in double quotes (Fig. 3)
=FLOOR(D2,“00:15”)
In this case, we always want to round down. It means that even when we go to halfway, we still go down. What’s more, even if we are very close to the next multiple, like in the last but one example, we still round down. Even, when we pass the multiple, we still round down, but this time to the next multiple, as in the last example (Fig. 4)
When we want to round up, we can use the CEILING function. We write the same arguments as in the previous functions. In this case, it’s enough if we pass the previous multiple by only one second, and the rounding will go up. Moreover, when we pass the next multiple by only a tiny bit, let’s say one hundredth of a second, we will go up to the next multiple (Fig. 5)
Summing up, when we use the CEILING function, we always go up. When we use the FLOOR function, we always go down. And when we use the MROUND function, we go to the nearest multiple. The most important is the middle point. We can, of course, write any time we want in the second argument of those functions.