How to Round Time Values in Excel?

Spread the love

In this post, you will learn how to round time values in excel.

Rounding time values –

It’s often necessary to round time to a particular increment. For instance, if you’re a con-
sultant, you may always want to round time up to the next 15-minute increment or down to
30-minute increments.

Figure below demonstrates how you can round to 15- and 30-minute increments.

The formula in cell E4 is as follows:

=ROUNDUP(C4*24/0.25,0)*(0.25/24)

The formula in cell F4 is as follows:

=ROUNDDOWN(C4*24/0.5,0)*(0.5/24)

You can round a time value to the nearest hour by multiplying the time by 24, passing that
value to the ROUNDUP function, and then dividing the result by 24. For instance, this for-
mula would return 7:00:00 AM:

=ROUNDUP("6:15:27"*24,0)/24

To round up to 15-minute increments, you simply divide 24 by 0.25 (a quarter). This formula
would return 6:30:00 AM:

=ROUNDUP("6:15:27"*24/0.25,0)*(0.25/24)

To round down to 30-minute increments, divide 24 by 0.5 (a half). This formula would
return 6:00:00 AM:

=ROUNDDOWN("6:15:27"*24/0.5,0)*(0.5/24)

Rating: 1 out of 5.

Leave a Reply