How to Round Time to the nearest 15 minute in Excel
There may be instances in Excel where we will need to round time up by a certain degree. Whether it be to the nearest 5 minute, 10 minute, 15 minute or 30 minute. A client of ours had such a task for us in mind where they have time values in a report and wanted them to be rounded up to the nearest 15 minute. For this task, we will use the both the CEILING and ROUND functions in our formula.
Assuming that your time is stored in cell A1, then enter the following formula in cell B1:
=CEILING(ROUND(A1*24*60,0),15)/24/60
Replace A1 with your specific cell address and 15 with the amount that you want it to round up to. You should now be set! This should work in Excel 97, 2000, 2003 and 2007.
- How to Use the CEILING function to round up values to the nearest 100
- Use the FLOOR function to round down values to the nearest 100
- Use the MROUND function to round values to the nearest multiple of 10 or 50
- How to Use the ROUND function to round numbers in Excel
- How to Use the HOUR function to calculate with 100-minute hours
