Converting Excel Time

[gareth001]

Hi,

I've got a time stored in seconds (eg 150 seconds), but how can I display the time in minutes and seconds (2:30)?

Excel help surgests using: =TEXT(A2/24, "h:mm") but I can't get this to work with mins and secs.

Thanks

Gareth

 

[SkipVought]

Time is a component of DATE. Convert seconds to days and then Format/Cells - Number Tab -- any time format.

Do NOT use =TEXT as you posted as this is NOT a time value. Rather it is a TEXT STRING of a time value that you cannot do arithmetic on.

Why do Dates and Times seem to be so much trouble? faq68-5827

Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}

 

[AnotherHiggins]

I strongly urge you read Skip's FAQ about how times work in Excel, but to answer your question directly:

150 represents 150 days. So 150/24=150 hours.

There are 60 hours in a minute. 24*60=1440. So 150/1440=150 Minutes.

There are 60 minutes in a second. 24*60*60=86400. So 150/86400=150 seconds.

Let's say your 150 is in cell A1. In cell A2, type in
[COLOR=blue white]=A1/86400[/color]
The answer will be [COLOR=blue white]0.001736[/color].
Now right click in that cell and choose Format Cells. Select Custom from the list on the left. In the box at the top right, type in m:ss.

Now the cell displays 2:30 and is actually stored as time, so you can do arithmetic on!

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[gareth001]

Thanks John, thats exactly what I was after.