Time transformation

**lza** · 12-02-2009, 09:25 AM

Hi

In the cell I have a text with minutes and seconds (34:29).
In some cells the minutes are above 60 (101:56).
I would like to convert to hours, minutes and seconds:
(mm:ss) 101:56 -> (hh:mm:ss) 01:41:56

Many thanks

**darkyam** · 12-02-2009, 10:53 AM

Go to Format Cells, then choose a time format or use a custom format of [h]:mm:ss

**lza** · 12-03-2009, 06:31 AM

It works if the cell contains 0:101:56, but not 101:56...

**DonkeyOte** · 12-03-2009, 06:44 AM

That's because 101:56 is 101 hours and 56 minutes - in which case you will need to divide your values by 60 before applying the format previously advised

(you can do this by entering 60 in a blank cell, copy and paste special : divide over your original values - or use adjacent cells, ie B1: =A1/60 format as [hh]:mm:ss)