CODEWITHSUNDEEP
sqlexceltime-format
Sewder
Sewder

Reputation: 754

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

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!

Upvotes: 0

Views: 2048

Answers (2)

gtwebb
gtwebb

Reputation: 3011

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

Upvotes: 1

Gary's Student
Gary's Student

Reputation: 96753

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

Upvotes: 0

Related Questions