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 at 9:07

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, privacy policy and cookie policy

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