Round timestamps down to a step

Turn 1/1/2027 7:15:30 AM into 7:10:00 AM on a 10-minute grid. Use 5, 10, 15, 30 or 60.

What you'll make

In Excel: 1/1/2027 7:15:30 AM, 10 minutes becomes 1/1/2027 7:10:00 AM.

The formula

The formula, with a numbered bracket under each part.
NumberPartWhat it doesResult so far
1A8The date and time.11/4/2026 2:30:15 PM
2B8/1440Turns the minutes into part of a day.5 minutes
3FLOOR.MATH( … )Rounds down to the step before.11/4/2026 2:30:00 PM

Excel counts time in days, and a day has 1,440 minutes. A time already on a step stays the same.

=FLOOR.MATH(A8,B8/1440)

How to use it

Yellow bar at the top? Click Enable Editing.

  1. See it work. The Example tab shows 20 made-up examples with the formula already in.
  2. Use your own dates and times. Paste dates with times into the green column and the minute step next to each. The rounded times fill in by themselves.
  3. Move them into your letter. Copy the results. Then use Paste Special and choose Values.

Before you send

Use a step that fits a day evenly, like 5, 10, 15, 30 or 60.

Microsoft's help: FLOOR.MATH function