Round timestamps to a minute step

Turn 1/1/2027 7:15:30 AM into 7:20: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:20: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
2*1440/B8Counts the steps since 1900. A day has 1,440 minutes.Steps since 1900
3ROUND( … ,0)Rounds to the nearest whole step.Whole steps
4*B8/1440Turns the steps back into a date and time.11/4/2026 2:30:00 PM

Read it from the inside out: ROUND works first, then *B8/1440 makes it a date and time again.

=ROUND(A8*1440/B8,0)*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

A time exactly halfway rounds up. Use a step that fits a day evenly, like 5, 10, 15, 30 or 60.

Microsoft's help: ROUND function