Round up duration calculation to the next multiple of five minutes

Question

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.

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.