Mrinmoy Roy
Suppose, you have a list of timestamps of each time a new data entry occurs in your worksheet. Now to analyze the data entry frequency, you want to round off your timestamps to the nearest 15 minutes. Well, there are several ways to do that. In this article, you will learn 6 quick methods to round time to the nearest 15 minutes in Excel.
Download Practice Workbook
You can download the Excel file from the following link and practice along with it.
6 Methods to Round Time to Nearest 15 Minutes in Excel
1. Using MROUND Function to Round Time to Nearest 15 Minutes
You can use the MROUND function to round your time to the resting 15 minutes quite easily.
For that,
❶ Insert the following formula in cell C5.
=MROUND(B5,"0:15")Here,
- Cell B5 contains the sampling timestamp.
- “0:15” specifies that the time interval will be 15 minutes.
❷ Then press ENTER.
❸ Drag the Fill Handle icon from cell C5 to C12 to copy the formula.
Now you will see, that all of your times have been rounded off to the nearest 15 minutes.
Here, the MROUND function rounds off a time to its nearest multiple of 15 minutes. For example, the multiples of 15 minutes close to 7:10 AM are 7:00 AM and 7:15 AM. Here, 7:15 AM is the closest to the 7:10 AM than 7:00 AM. Thus, 7:10 AM turns out 7:15 AM instead of 7:00 AM.
For the same reason, 8:19 AM becomes 8:15 AM, 9:22 AM becomes 9:15 AM, and so on.
Read More: How to Round Time to Nearest Minute in Excel (5 Suitable Ways)
2. Using CEILING Function to Round Time to Next Nearest 15 Minutes
The CEILING function rounds up a number to its next closest integer value. You can use this function to round off time to the next nearest 15 minutes in Excel.
For that,
❶ Insert the following formula in cell C5.
=CEILING(B5,"0:15")Here,
- Cell B5 contains the sampling timestamp.
- “0:15” specifies that the time interval will be 15 minutes.
❷ Then press ENTER.
❸ Drag the Fill Handle icon from cell C5 to C12 to copy the formula.
After that, all of your times have been rounded off to the next nearest 15 minutes of the timestamp.
Here, the CEILING function rounds off a time to its next nearest multiple of 15 minutes. For example, the multiples of 15 minutes close to 7:10 AM are 7:00 AM and 7:15 AM. Here, 7:15 AM is the next closest to the 7:10 AM whereas, 7:00 AM is the previous closest. Thus, 7:10 AM turns out 7:15 AM instead of 7:00 AM.
For the same reason, 8:19 AM becomes 8:30 AM, 9:22 AM becomes 9:30 AM, and so on.
Read More: How to Round Time in Excel (With 3 Examples)
Similar Readings
- How to Round a Formula with SUM in Excel (4 Simple Ways)
- Round off Formula in Excel Invoice (9 Quick Methods)
- How to Round Excel Data to Make Summations Correct (7 Easy Methods)
3. Round Time to Immediate Previous Nearest 15 Minutes Using FLOOR Function
The FLOOR function rounds off a number to its previous nearest integer value. However, this function can also be used to round off a time its immediate nearest 15 minutes.
To do that,
❶ First, Insert the following formula in cell C5.
=FLOOR(B5,"0:15")Here,
- Cell B5 contains the sampling timestamp.
- “0:15” specifies that the time interval will be 15 minutes.
❷ Then press ENTER.
❸ Now, drag the Fill Handle icon from cell C5 to C12 to copy the formula.
Finally, you will see that all of your timestamps have been rounded off to their immediate nearest 15 minutes.
Here, the FLOOR function rounds off a time to its previous nearest multiple of 15 minutes. For example, the multiples of 15 minutes close to 7:10 AM are 7:00 AM and 7:15 AM. Here, 7:00 AM is the previous closest to 7:10 AM whereas, 7:15 AM is the next closest. Thus, 7:10 AM turns out 7:00 AM instead of 7:15 AM.
For the same reason, 8:19 AM becomes 8:15 AM, 9:22 AM becomes 9:15 AM, and so on.
Read More: Round Time to Nearest 5 Minutes in Excel (4 Quick Methods)
4. Using ROUND Function to Round Time to Nearest 15 Minutes
The ROUND function is a general-purpose function to round off numbers. However, this function can be used to round off a time value to its nearest 15 minutes in Excel.
To do that,
❶ Insert the following formula in cell C5.
= (ROUND((B5*1440)/15, 0)*15)/1440Here,
- Cell B5 contains the sampling timestamp.
- The timestamp is multiplied by 1440 to convert the time into minutes.
- Then it is divided by 15 to count the chunks of 15 minutes in the timestamp.
- 0 is used to remove all the digits after the decimal point.
- Finally, it is multiplied by 15 and later divided by 1440 again to round the time to the nearest 15 minutes.
❷ Then press ENTER.
❸ Drag the Fill Handle icon from cell C5 to C12 to copy the formula.
Now you will see, that all of your times have been rounded off to the nearest 15 minutes.
Here, the ROUND function rounds off a time to its nearest multiple of 15 minutes. For example, the multiples of 15 minutes close to 7:10 AM are 7:00 AM and 7:15 AM. Here, 7:15 AM is the closest to the 7:10 AM than 7:00 AM. Thus, 7:10 AM turns out 7:15 AM instead of 7:00 AM.
For the same reason, 8:19 AM becomes 8:15 AM, 9:22 AM becomes 9:15 AM, and so on.
Read More: Rounding Time to Nearest Quarter Hour in Excel (6 Easy Methods)
Similar Readings
- Rounding to Nearest Dollar in Excel (6 Easy Ways)
- Stop Excel from Rounding Large Numbers (3 Easy Methods)
- How to Roundup a Formula Result in Excel (4 Easy Methods)
5. MOD Function to Round Time to Immediate Previous Nearest 15 Minutes
Here, I will show you how to round time to the nearest 15 minutes in Excel using the MOD function.
For that,
❶ Insert the following formula in cell C5.
=B5-MOD(B5,15/24/60)Here,
- Cell B5 contains the sampling timestamp.
- 15/24/60 is the divisor.
❷ Then press ENTER.
❸ Drag the Fill Handle icon from cell C5 to C12 to copy the formula.
Now you will see, that all of your times have been rounded off to the nearest 15 minutes.
Here, the formula of the MOD function rounds off a time to its previous nearest multiple of 15 minutes. For example, the multiples of 15 minutes close to 7:10 AM are 7:00 AM and 7:15 AM. Here, 7:00 AM is the previous closest to 7:10 AM whereas, 7:15 AM is the next closest. Thus, 7:10 AM turns out 7:00 AM instead of 7:15 AM.
For the same reason, 8:19 AM becomes 8:15 AM, 9:22 AM becomes 9:15 AM, and so on.
Read More: Rounding Time in Excel to Nearest Hour (6 Easy Methods)
6. Using TIME, ROUND, HOUR, and MINUTE Functions to Round Time
In this section, I will show you how to round time to the nearest 15 minutes by combining the TIME, ROUND, HOUR, and MINUTE functions in Excel.
For that,
❶ Insert the following formula in cell C5.
=TIME(HOUR(B5),ROUND((MINUTE(B5)/60)*4,0)*15,0)Here,
- Cell B5 contains the sampling timestamp.
- HOUR(B5) extracts hours from cell B5.
- MINUTE(B5)/60)*4,0) extracts minutes from cell B5.
- ROUND((MINUTE(B5)/60)*4,0) rounds off the value returned by MINUTE(B5)/60)*4,0).
- TIME(HOUR(B5),ROUND((MINUTE(B5)/60)*4,0)*15,0) converts the fraction number output HOUR(B5),ROUND((MINUTE(B5)/60)*4,0)*15 into time format.
❷ Then press ENTER.
❸ Drag the Fill Handle icon from cell C5 to C12 to copy the formula.
Now you will see, that all of your times have been rounded off to the nearest 15 minutes.
Here, the combination of the TIME, ROUND, HOUR, & MINUTE functions rounds off a time to its nearest multiple of 15 minutes. For example, the multiples of 15 minutes close to 7:10 AM are 7:00 AM and 7:15 AM. Here, 7:15 AM is the closest to the 7:10 AM than 7:00 AM. Thus, 7:10 AM turns out 7:15 AM instead of 7:00 AM.
For the same reason, 8:19 AM becomes 8:15 AM, 9:22 AM becomes 9:15 AM, and so on.
Read More: Round to Nearest 5 or 9 in Excel (8 Easy Methods)
Conclusion
To sum up, we have discussed 6 methods to round time to the nearest 15 minutes in Excel. You are recommended to download the practice workbook attached with this article and practice all the methods. And don’t hesitate to ask any questions in the comment section below. We will try to respond to all the relevant queries asap. And please visit our website Exceldemy to explore more.
