How to round time to 30 minutes or 15 minutes in Excel
With time statistics such as working time, overtime, we will have a rounding function to bring the rounded time value to half an hour or 15 minutes. Such a rounding will be useful in you summarizing time, calculating more favorable time. The following article will guide readers to use the MOD function to round time to 30 minutes and MOD to round time to 30 minutes.
1. How to use the MOD function to round time to about half an hour
At the time table to be rounded, in the first time input box enter the formula = MOD (INT (B2 / (1/48)) * (1/48), 1) where B2 is the value cell time wants to be rounded.
How to round time to 30 minutes or 15 minutes in Excel Picture 1
Output the decimal number as shown below. Next you need to change the format for this cell to Time is okay. You click on the results box and then in the Number group on the toolbar select the format as Time as shown.
How to round time to 30 minutes or 15 minutes in Excel Picture 2
Now the cell has been converted to the time format and rounded to half an hour ago as shown. Now we just need to drag the first cell result down to the remaining cells.
How to round time to 30 minutes or 15 minutes in Excel Picture 3
2. How to use the FLOOR function rounds time to about 15 minutes
To round the time back to 15 minutes ago, we will use the FLOOR function. In the first cell enter the result of rounding time to 15 minutes, enter the formula = FLOOR (B2; TIME (0; 15; 0)) . Note that if you want to round to another value, then enter that time in the formula is okay.
How to round time to 30 minutes or 15 minutes in Excel Picture 4
If the result shows a decimal, you also need to change the format to Time.
How to round time to 30 minutes or 15 minutes in Excel Picture 5
If you want, you can also round the time back to 20 minutes ago with the same formula.
How to round time to 30 minutes or 15 minutes in Excel Picture 6
And you also get the result of the time rounding.
How to round time to 30 minutes or 15 minutes in Excel Picture 7
You should read it
- Round function, how to use rounded functions in Excel
- Round a number in Excel (ROUND function)
- Ways to round numbers in Excel
- Round function (rounding) in Excel
- Instructions on how to use the ROUND function in excel
- Do not round numbers in Excel, turn off rounding in Excel
- ROUND function in SQL Server
- Basic Excel functions that anyone must know
- How to use Hlookup function on Excel
- Compare ROUND, ROUNDUP and ROUNDDOWN rounding functions in Excel
- How to use the SUM function to calculate totals in Excel
- How to use the LEN function in Excel
May be interested
Misa: App effectively manage spending on phone and usage
How to turn off 'seen' when reading Instagram messages
How to take screenshots of the game screen on Steam
How to watch and download TikTok videos on Windows 10
How to install TutuApp on iOS and Android
How to share an estimated arrival time on Apple Maps