Convert seconds to display minutes and seconds 1

[RachelD]

Hi - I have a column showing duration in seconds, and I want it to display in minutes and seconds (instead of 78 seconds, I want to see 1 minute 18 seconds).

How do I do this simply? (excel 2002)
thanks
Rachel

 

[nberryman]

Format Cells and on the Number tab select custom off the bottom of the list. Scroll down the Right hand pane and select the format [h]:mm:ss.

This should roll over times
Neil Berryman
IT Trainer
neil_berryman@btopenworld.com

 

[RachelD]

I had already tried this but it did not convert properly. For example, 66 seconds converted to 1584:00:00.

 

[nberryman]

Are the figures the result of a formula based on times or are they just numbers type into the cell?

Neil Berryman
IT Trainer
neil_berryman@btopenworld.com

 

[RachelD]

Just numbers typed into a cell. They are currently formatted as General.

 

[nberryman]

Try looking at Microsoft.com knowledge base for Q214095

Get back if thats no help Neil Berryman
IT Trainer
neil_berryman@btopenworld.com

 

[xlbo]

Hi Rachel - try this formula
=INT(A1/60)&"m "&A1-(INT(A1/60)*60)&"s"

where your time in seconds is in A1. This will produce
1m 6s for 66 or 1m 18s for 78

If you want the words "Minute(s)" and "Second(s)" you'll need an IF statement to check whether it's 1 or more than 1 and adjust the grammer appropriately

HTH
~Geoff~