Round time in Excel

Here is how to round time in Excel in different ways depending on how necessary. For example, you can round time down or up to the nearest 15-minute interval.

Here is my DateTime in cell A1 which I will be using in different examples below.

02.12.2022  17:58:00

Round time down to the nearest interval in Excel

By using Excel functions FLOOR or younger version FLOOR.MATH, you can round time down to the nearest 15-minute or another interval. By using a significance argument, you can specify the necessary time interval.

=FLOOR(A1, "0:15")

The result, based on my cell A1 content, looks like this.

02.12.2022  17:45:00

Round time up to the nearest interval in Excel

By using Excel functions CEILING or younger version CEILING.MATH, you can round time up to the nearest 15-minute or another interval. By using a significance argument, you can specify the necessary time interval.

=CEILING(A1, "0:15")

The result, based on my cell A1 content, looks like this.

02.12.2022  18:00:00

Build interval category in Excel

Imagine that you want to represent a time interval by showing a categorical value with the beginning and the end.

To do that, you should combine the results of both previously seen functions FLOOR and CEILING. If there is DateTime, then it is necessary to get only time. If you want to extract time from DateTime in Excel, then here is how to do that.

After that, you can create a time interval category by concatenating and formatting time like this.

=TEXT(B2, "hh:mm:ss") & " - " & TEXT(C2, "hh:mm:ss")

Rounding up or rounding down are the situations that are relevant for me. If you want to round simply to the nearest interval, try to use Excel function MROUND.

Thank you for reading this post, and I invite you to take a look at other Excel-related posts in this blog.