How to Round Minutes to the Quarter Hour in Excel

Rounding Minutes to the Nearest Quarter Hour

Someone asked me how to round minutes to the quarter hour in Excel and although there’s no built-in function to do this, you can make Excel do it for you with a few nested IF statements.  Here’s how to do it.

I first had to decide how we were going to round.  This is what I decided:

• if the minutes are 0-7 it returns 0
• for minutes of 8-22 it returns 15
• for minutes of 23-37 it returns 30
• for minutes 38-52 it returns 45
• and everything else it returns 60 (which would mean you need to increment the hour)

The formula is as follows (assuming B4 holds an integer that stands for the minutes between 0 and 60):

=IF(B4<8,0,IF(B4<23,15,IF(B4<38,30,IF(B4<53,45,60))))

If you wanted to round the time to a decimal (portion of an hour) you would do it as follows instead:

=IF(B4<8,0,IF(B4<23,.25,IF(B4<38,.5,IF(B4<53,.75,1))))

For this second formula:

• if the minutes are 0-7 it returns 0
• for minutes of 8-22 it returns .25
• for minutes of 23-37 it returns .5
• for minutes 38-52 it returns .75
• and everything else it returns 1 (which would mean you need to increment the hour)

If you need to extract the minutes from a cell that holds the time you can use the following function:

=MINUTE(B4)

You could integrate that Minute extracting formula with the decimal formula above by writing the following:

=IF(minute(B4)<8,0,IF(minute(B4)<23,.25,IF(minute(B4)<38,.5,IF(minute(B4)<53,.75,1))))

Note, the formula above is all one continuous line, with no spaces or returns (even if it wraps around to another line in your browser).