Excel FLOOR Formula for Time: Round Down by Minutes

Formulas & functions · Updated November 18, 2026

To round an Excel time down to the nearest 15 minutes, use =FLOOR.MATH(A2,TIME(0,15,0)), assuming the time is in A2. The formula returns a numeric time rounded to the previous 15-minute boundary; format the result cell as a time to display it clearly.

What FLOOR does to an Excel time

Excel stores times as fractions of a day: midnight is zero, noon is one-half, and a full day is one. A time that looks like 10:37 is therefore a decimal value underneath, even though the cell displays hours and minutes. The FLOOR.MATH function rounds a number down to a multiple of a chosen significance. For time, that significance is another fraction of a day.

In the opening example, TIME(0,15,0) represents 15 minutes. The nearest lower multiple of that interval before 10:37 is 10:30, so the result displays as 10:30. FLOOR.MATH does not merely hide the extra minutes: it changes the underlying numeric value, which means the result can be used in calculations, sorting, and comparisons.

Here, down means toward the lower multiple, not necessarily toward zero. This distinction matters with negative numbers, but most ordinary time-of-day values are positive. FLOOR.MATH is available in modern Excel, including Microsoft 365. It is generally a clearer choice than the older FLOOR function when you specifically mean mathematical rounding down.

Round down to a chosen minute interval

Choose an interval based on what the rounded time needs to represent. Fifteen-minute blocks are common for timesheets and appointment schedules; six-minute blocks can be useful where time is recorded in tenths of an hour. The interval should be entered as a valid Excel time value, not as a number of minutes alone.

  • For 5-minute blocks, use an interval of five minutes.
  • For 10-minute blocks, use an interval of ten minutes.
  • For 30-minute blocks, use an interval of half an hour.
  • For whole-hour blocks, use an interval of one hour.

With the source time in A2, the following formula rounds it down to the nearest 10 minutes. For example, 10:37 becomes 10:30, while 10:40 remains 10:40.

With a time in A2, this formula returns the previous 10-minute boundary: =FLOOR.MATH(A2,TIME(0,10,0)).

You can also keep the interval in a cell so it is easy to change. Enter a valid Excel time interval in B1, such as 00:15, and put the source time in A2. This version rounds down to the interval stored in B1:

With the source time in A2 and the interval in B1, use =FLOOR.MATH(A2,$B$1).

The dollar signs keep the interval reference fixed if you copy the formula down a column. Make sure B1 contains a real time value, not text that only looks like a time. You can enter 00:15 directly in a cell and format it as a time, or use a formula to create the interval.

Use TIME to build the interval

TIME(hour,minute,second) creates an Excel time value, which makes the interval in the formula easier to read and adjust. The hour, minute, and second arguments are numbers. For example, a 45-minute interval can be expressed with a zero-hour value, 45 minutes, and zero seconds.

With the time to round in A2, this formula rounds down to a 45-minute multiple: =FLOOR.MATH(A2,TIME(0,45,0)). A time of 11:20 becomes 10:30, because 10:30 is the largest 45-minute multiple below it from the start-of-day baseline.

That last detail is important: FLOOR.MATH uses multiples measured from zero. It does not automatically begin counting intervals from the first time in your shift, the first appointment, or an arbitrary starting point. If your blocks must begin at a different anchor, account for that separately.

Round timestamps and shift-based times

A cell can contain both a date and a time, even if its format displays only the time. The same flooring method rounds the complete date-time number. For common intervals that divide evenly into 24 hours, such as 15 minutes or one hour, the boundaries line up with the clock each day. A timestamp at 10:37 on a particular date rounds to 10:30 on that same date when the interval is 15 minutes.

For a shift schedule whose intervals begin at a particular anchor, subtract the anchor, round the difference, and add the anchor back. Suppose A2 contains a timestamp, B1 contains the interval, and C1 contains the date-time anchor for the schedule. The formula below rounds down to a boundary measured from C1:

With the timestamp in A2, interval in B1, and schedule anchor in C1, use =FLOOR.MATH(A2-$C$1,$B$1)+$C$1.

For example, if the anchor is 06:00 and the interval is 15 minutes, a time of 10:37 rounds to 10:30. Use an anchor appropriate to the date range in your data. A fixed date-time anchor creates a continuous sequence of intervals from that point; it is not automatically reset to a new daily starting time.

For intervals that do not divide evenly into a day, such as seven hours, multiples from zero can fall at different clock times on successive dates. That is a consequence of measuring a seven-hour interval continuously across the date-time serial values. If the business rule requires a daily reset, calculate relative to a suitable daily anchor rather than assuming ordinary flooring will reset at midnight.

Format the result so it looks right

The formula result is a number, so Excel may show a decimal or inherit an unexpected format from the destination cell. Select the result cells and choose Home > Number Format, then select a time format such as 1:30 PM or 13:30. You can also use Ctrl+1 on Windows to open Format Cells and choose a time format. On Mac, use Command+1 to open Format Cells.

If the values represent durations that can exceed 24 hours, use a custom format such as [h]:mm. Ordinary hour formats typically display the hour within a 24-hour clock, which can make a 27-hour duration appear like 3:00. The brackets in [h]:mm tell Excel to show total elapsed hours instead. This display choice does not change the underlying value or the FLOOR calculation.

When you see a decimal after entering the formula, that is usually a formatting issue rather than a failed calculation. Applying a time format changes how the numeric result is displayed. Conversely, formatting a cell as time does not convert text into a genuine Excel time value.

FLOOR versus rounding up or to nearest

Use FLOOR.MATH when the rule is specifically to assign a time to the interval that has already begun. For example, a 10:37 clock-in rounded down to 15-minute blocks becomes 10:30. This can be appropriate for some reporting rules, but rounding policy can have workplace, payroll, or contractual implications; the formula alone does not determine which policy is appropriate.

To round upward instead, use CEILING.MATH. With a time in A2, this formula rounds 10:37 up to 10:45 for a 15-minute interval:

With the source time in A2, use =CEILING.MATH(A2,TIME(0,15,0)).

For the nearest interval rather than always down or always up, use MROUND. With the same 15-minute interval, 10:37 rounds to 10:30, while a time closer to 10:45 rounds to 10:45. At an exact halfway point, MROUND rounds away from zero.

With the source time in A2, this formula rounds to the nearest 15 minutes: =MROUND(A2,TIME(0,15,0)).

These functions answer different questions. FLOOR.MATH finds the previous boundary, CEILING.MATH finds the next boundary, and MROUND finds the closest one. Pick the function that matches the actual rule, rather than choosing based on which result looks most convenient.

Common errors and checks

If Excel returns an error or an unexpected display, check the values and the interval before changing the formula. A rounding formula cannot reliably interpret every value that merely looks like a time.

  • The source is text: A2 may contain text such as a time imported from another system. Convert it to a real Excel time value before relying on arithmetic. Changing its number format alone will not convert text.
  • The interval is zero or invalid: The significance must be a nonzero numeric interval. A zero interval cannot define meaningful multiples.
  • The result looks like a decimal: Apply a time format to the result cell.
  • The result appears on the wrong date: Check whether the source includes a date, whether the interval is anchored at midnight, and whether your schedule uses a different starting point.
  • The result seems to jump by an unexpected amount: Verify that the interval cell contains the intended duration. Excel stores times as fractions of a day, so a raw number may represent a different interval than expected.

It is also worth checking a few boundary cases before filling a formula down a large dataset. Test a value exactly on a boundary, one minute before it, and one minute after it. For a 15-minute interval, times such as 10:30, 10:29, and 10:31 should produce 10:30, 10:15, and 10:30 respectively. These checks help distinguish a wrong interval or anchor from a simple formatting problem.

Frequently Asked Questions

What is the Excel formula to round time down to 15 minutes?
With the time in A2, use =FLOOR.MATH(A2,TIME(0,15,0)). Format the result as a time; a value such as 10:37 displays as 10:30.

Can I use FLOOR instead of FLOOR.MATH?
In many ordinary positive-time cases, the older FLOOR function can also round down to a time interval. FLOOR.MATH makes the intended mathematical direction clearer and is generally preferable in current Excel. The functions can differ in how they handle negative numbers, so avoid substituting one without checking that behavior if your data includes negative values.

How do I round time down to the nearest hour?
With the time in A2, use =FLOOR.MATH(A2,TIME(1,0,0)). A time of 10:37 becomes 10:00. If the value includes a date, the date remains part of the rounded date-time result.

Why does FLOOR.MATH show a decimal number?
Excel stores the result as a numeric fraction of a day. Apply a time number format to display hours and minutes. The formula can still return a correct numeric value even when the cell is not formatted for human-readable time.

How do I round down from a shift start instead of midnight?
Subtract a schedule anchor before rounding, then add it back. With a timestamp in A2, interval in B1, and anchor in C1, use =FLOOR.MATH(A2-$C$1,$B$1)+$C$1. Set the anchor to the correct starting date and time for the schedule.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.