Excel Convert Time HH:MM:SS to Time elapsed MM:SS

Question

The data I have is formated as HH:MM:SS where the seconds are all 00, so a time like

     12:22:00

Should be

     00:12:22  

Is there a way I can convert this over in excel?

EDIT:

Probably a better example.

A time of zero. So that means no time was elapsed shows up as

 12:00:00 AM

Instead OF

  00:00 (MM:SS)

Thanks!

Answer 1

You could just divide your times by 60.

Put 60 in a cell, copy it, select the range you want and pastespecial with divide. Also you should just paste values if you want to keep your formatting.

Gordon

Answer 2

Select the cells you want to change and run this:

Sub FixTime()
Dim r As Range
For Each r In Selection
    v = r.Value
    h = Hour(v)
    m = Minute(v)
    r.Value = TimeSerial(0, h, m)
Next r
End Sub