How to Round Time to Nearest 15 Minutes in Excel (6 Quick Methods)

Get FREE Advanced Excel Exercises with Solutions!

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.


How to Round Time to Nearest 15 Minutes in Excel: 6 Methods

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.

Using MROUND Function to Round Time to Nearest 15 Minutes

❸ 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.

Output of Using MROUND Function to Round Time to 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


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.

Using CEILING Function to Round Time to Next Nearest 15 Minutes

❸ 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.

Output of Using CEILING Function to Round Time to Next Nearest 15 Minutes

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


3. Round Time to Immediate Previous Nearest 15 Minutes Using FLOOR Function

The FLOOR function rounds down 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.

Round Time to Immediate Previous Nearest 15 Minutes Using FLOOR Function

❸ 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. 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)/1440

Here,

  • 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.

Using ROUND Function to Round Time to Nearest 15 Minutes

❸ 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.

Output of Using ROUND Function to Round Time to 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


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.

MOD Function to Round Time to Immediate Previous Nearest 15 Minutes

❸ 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.

Output of Using MOD Function to Round Time to Immediate Previous 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. 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.

Using TIME, ROUND, HOUR, & MINUTE Functions to Round Time

❸ 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.


Download Practice Workbook

You can download the Excel file from the following link and practice along with it.


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.


Related Articles


<< Go Back to Round Time in Excel | Rounding in Excel | Number Format | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Mrinmoy Roy
Mrinmoy Roy

Mrinmoy Roy, a dedicated professional with a BSc in Electronics and Communication Engineering from Khulna University of Engineering & Technology, Bangladesh, brings over two years of expertise to the ExcelDemy project. As a prolific contributor, he has authored around 180 articles, showcasing his deep knowledge and passion for Microsoft Excel, Data Analysis, and VBA. His unwavering commitment to continuous learning, combined with versatile skills, renders him well-suited for roles in data management and spreadsheet solutions. He has interest... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo