I currently have this formula:

=text(D2 - C2, "hh:mm:ss")

I need this formula to round up to the nearest 5 minutes.

I have tried mround(), ceiling() and roundup(), but I'm not getting the result I need.

  • The requirement to use text() sounds superfluous. Please confirm whether you need the result to be a duration, or a text string that just looks like a duration. Aug 24, 2021 at 9:07

1 Answer 1


Try this:

=mround((D2 - C2) * 24 * 60 + 5, 5) / 24 / 60

Format the result cell as Format > Number > Duration or another time number format of your choosing.

See this answer for an explanation of how date and time values work in spreadsheets.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.