Time in Excel round - so it works
Suppose you want to round up a time 05:20 p.m. on the hour. Since times are not expressed in the ten system, you need to use a little Trick. Multiply the time with the number of hours a day, so 24. You can then round the result and again by 24.
- The formula is "=ROUNDUP(05:20*24;0)/24". In the clip you first enter the time and multiply this with 24. Then enter the number of desired decimal places. The rounded result then divide again by 24.
- Of course, you can use cell references, for example: "=ROUND(A3*24;0)/24"
- You can round exactly. For half-hours, multiply, and divide with 48, for a quarter-hour to 96, for ten minutes with 144, for five minutes with 288.
- If you want to round off, to write "ROUNDING"(...)"; depending on the trend up or down you write "ROUND(...)".
Time rounded to 5 minutes
In the next practical tip read, as in Excel formulas transfer.
