Excel: Rounding the time up and down

Excel can be used to round numbers with a simple formula as precisely as desired. At times it gets a bit more complicated. A trick will help you with this.

Round the time in Excel - so it'll work

Suppose you want to round up a time like 05:20 to the hour. Since times are not expressed in the tens system, you have to use a little trick for this. Multiply the time by the number of hours that make up a day, i.e. 24. Then you can round the result and divide it again by 24.

• The formula is "= ROUND UP (05: 20 * 24; 0) / 24". First enter the time in brackets and multiply this by 24. Then enter the number of decimal places you want. Then divide the rounded result by 24 again.
• Of course, you can also use cell references as usual, for example: "= ROUND UP (A3 * 24; 0) / 24"
• You can round exactly as you like. Multiply and divide for half an hour by 48, for quarter hours by 96, for ten minutes by 144, for five minutes by 288.
• If you want to round down, write "ROUND DOWN" (...) "; to round up or down round you write" ROUND (...) ".

In the next practical tip you will read how to transfer formulas to Excel.