converting seconds to hours, minutes, seconds

admiral_Tristan · Jan 28, 2004

Hello,

I have a cell which contains a total amount of seconds. I need this number expressed in terms of hours,minutes,and seconds logged in. Now my problem is that sometimes the seconds may not total 1 hour or even 1 minute, so I need the formula to then just display the seconds. IE

123543--expressed in hours, minutes, seconds
53--would just show minutes
5--would just show seconds.

Anyone know how I can do this?

just_jon · Jan 28, 2004

Always a 6-digit number if hours/minutes/seconds?

Zack Barresse · Jan 28, 2004

admiral_Tristan said:
I need this number expressed in terms of hours,minutes,and seconds... Anyone know how I can do this?

so I need the formula to then just display the seconds.

i don't understand. do you want just seconds? or days...hours...minutes...second?

Greg Truby · Jan 28, 2004

Would this do?

Book3

A

B

C

D

1

Seconds

2

Unformatted

Formatted

3

0

:mm:ss"))]:00

4

5

:05

5

50

:50

6

500

8:20

7

5000

1:23:20

8

25000

6:56:40

9

250000

69:26:40

Sheet1

Formula in B3:B9 is =TEXT(A5/86400,CHOOSE(MATCH(A5,{0,60,3600},1),":ss","m:ss","[h]:mm:ss"))

Alternate output:
=TEXT(A2/86400,CHOOSE(MATCH(A2,{0,60,3600},1),"s ""sec""","m ""min"" s ""sec""","[h] ""hrs"" m ""min"" s ""sec"""))

MartinK · Jan 28, 2004

That's a nice one Greg,

but since it took some effort before I found the right workbook on my HDD, I'll post it anyway...

123456 is:
1 day(s), 10 hour(s), 17 minutes, 36 seconds
=INT(1/86400*A1) &" day(s), "& INT(MOD(A1;86400)/3600) & " hour(s), " & INT(MOD(A1;3600)/60) &" minutes, " & MOD(A1;60) &" seconds"

or
34 hour(s), 17 minutes, 36 seconds
= INT(A1/3600) & " hour(s), " & INT(MOD(A1;3600)/60) &" minutes, " & MOD(A1;60) &" seconds"

Martin

PaddyD · Jan 28, 2004

would, perhaps a simple a1/86400 & the appropriate format suffice?

Book1

A

B

C

D

1

123543

01101903

2

53

00000053

3

5

00000005

Sheet2

admiral_Tristan · Jan 28, 2004

greg you are the man, thats a great perfect formula. my manager is going to think I'm great now!!

thanks for all your help, I'm always amazed at the speed and quality of the replies on this board. truly excellent

Greg Truby · Jan 28, 2004

Happy to have been of assistance.

rrdonutz · Jan 28, 2004

Aside from Greg's slick formula, another approach would be to use custom formatting, as below--

MrE1254.xls

A

B

C

D

1

Seconds

Custom

2

Unformatted

Format

3

0

:00

CellsB3:B9customformat:

4

59

:59

[>0.041666][h]:mm:ss;[>0.000694]m:ss;\:ss

5

60