Do you want to round the working time to the nearest 15 minutes? I will show you how to do it.
Round time to nearest 15 minutes MROUND, FLOOR or CEILING functions
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”)
Fig. 1 MROUND function
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)
Fig. 2 Rounding with the MROUND function
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”)
Fig. 3 FLOOR function
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)
Fig. 4 Rounding with the FLOOR function
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)
Fig. 5 Rounding with the CEILING function
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.
Do you want to know how to round numbers? Follow me.
Round to nearest multiple MROUND, FLOOR or CEILING functions
Let’s assume I get paid for correcting text and I don’t want to work with tiny amounts, like individual cents. What may help me is rounding the number of characters checked by me in a given text. When I round it, my income will be rounded as well. Let’s assume that I want to round to the nearest multiple of 200. In standard Excel rounding, when we go to the middle of the number, which in our case is 100, the rounding starts to go up. It means that when we use, e.g. the MROUND function to round the nearest multiple of 200, in the case of 2349 we go up because we passed the threshold of 2300. However, in the case of 2499, we go down. Why? Because we haven’t reached the middle point. But when we reach the middle point, which in this example is exactly 2500, we go up. In order to go up, we only have to reach the middle point, and when we don’t reach this point we go down. It means that it’s even for me and for my customer (Fig. 1)
Fig. 1 Threshold and rounding
Sometimes, there are situations that I always want to round down or always round up. Let’s assume that rounding down will be a little bit nicer to my customer, and rounding up will be nicer for me. How can we do it? In Excel, when we always want to round down, we can use the FLOOR function (Fig. 2)
=FLOOR(D2,200)
Fig. 2 FLOOR function
We have to remember that we are still using the rounding to the nearest multiple of 200. Our rounding goes down even if we passed the halfway. Even, when we are very near and almost reached the point of the multiple, and even if the difference is a very small fraction of an integer, like in 2599.999, we still go down. And when we reach the nearest multiple, which is 3000 in our case, it becomes our base and we will still go to this base until we reach the next base, which is the next multiple of 200 (Fig. 3)
Fig. 3 Rounding down
When we want to always go up, we can use the CEILING function, where we also give it a number and significance, i.e. a multiple, as it was with the FLOOR function (Fig. 4)
=CEILING(G2,200)
Fig. 4 CEILING function
When we pass our next multiple, we always go up. Even if the number passed the nearest multiple by only a small fraction, like 30000.0001, we still go up (Fig. 5)
Fig. 5 Rounding up
Of course, 200 is only an example of a multiple for those functions. We can write there 500, or use even decimal numbers, like 0.5 or 1.5. However, the example above used simple, hole numbers.
