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

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.

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

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 (With 3 Examples)


Similar Readings


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.

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 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)/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 (6 Easy Methods)


Similar Readings


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

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.

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.


Related Articles

Mrinmoy

Mrinmoy

Hi! I'm Mrinmoy Roy. I'm an Excel and VBA content developer. I write blogs relating to Microsoft Excel on Exceldemy.com. I've completed my graduation in Electronics and Communication Engineering from Khulna University of Engineering & Technology. I've expertise in Excel functions, formulas, Pivot Table, Power Query, Visual Basic, etc. I write blogs to lessen people's hassles while working on Microsoft Excel.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo