Working with time values comes with its own challenges. One of the issues is how to round off time values from seconds to minutes or minutes to hours.
You can use the MROUND function to round off time values. The MROUND function returns a number rounded to the nearest instance of a specified multiple.
It’s easier to manipulate time values if they are rounded off. So, if you work with time values you will find this tip useful, especially HR personnel, IT security professionals, stock controllers etc.
Note: You are welcome to download the workbook to practice.
Applies To: Microsoft® Excel® 2010 and 2013
1.Start by formatting numbers to the time format.
- Highlight the range B3:B9.
- Right click and select format cells.
- Select custom under the number tab.
- Select h:mm:ss then select OK.
2.To round off to the nearest hour.
- Place the cursor in cell C3.
- Type =MROUND(B3,”1:00″).
- Press enter and copy the formula down.
3.To round off to the nearest minute.
- Place the cursor in cell D3.
- Type =MROUND(B3,”0:01″).
- Press enter and copy the formula down.
The time values have been rounded to the nearest hour or minute.Only minutes or seconds that are greater than or equal to 30 are rounded off.
