CODEWITHSUNDEEP
exceldate
300
300

Reputation: 1031

Using MS Excel, how to round date/time format value to nearest 5 minute?

I have a column with custom format (as the data comes from DB) as "m/d/yyyy h:mm". How can I add a formula to another column next to this one so I get result rounded up/down to nearest 5 minute value?

I want result in same format as "m/d/yyyy h:mm".

So for example:

6/20/2016 16:02 should be converted to-> 6/20/2016 16:05

6/20/2016 16:50 should be converted to-> 6/20/2016 16:50

I can use =MROUND(F24,5/(60*24)) but it gives output in mm:ss.0 format but I need it in "m/d/yyyy h:mm" format.

Upvotes: 0

Views: 1475

Answers (1)

ManishChristian
ManishChristian

Reputation: 3784

Assuming your data is in column A. Just use CEILING function like this: 

=CEILING(A2,"00:05")

Here is the data that I've used:

enter image description here

Place the formula in cell B2 and drag it down.

Upvotes: 0

Related Questions