How do I convert time listed in hours(e.g. 170:41:19) to seconds?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Red Data Man - 24 Aug 2006 19:27 GMT

I have a data cell that lists the sum of times, but it displays in a format
with colons in it. I want to convert that data to a SINGLE NUMBER. For
example, I would like to convert 170:41:19 to 614479 (the number of seconds
in 170 hours, 41 minutes and 19 seconds). I have more than 7000 of these
cells so I cannot do it manually. Any suggestions?

Reply to this Message

Dave F - 24 Aug 2006 19:35 GMT

I multiplied the time by 24 and then multiplied that by 3600.

Example: if 170:41:19 is in A1, then in B1: =(A1*24)*3600 = 614479. Make
sure B1 is formatted as numbers, not time.

Signature

Brevity is the soul of wit.

> I have a data cell that lists the sum of times, but it displays in a format
> with colons in it. I want to convert that data to a SINGLE NUMBER. For
> example, I would like to convert 170:41:19 to 614479 (the number of seconds
> in 170 hours, 41 minutes and 19 seconds). I have more than 7000 of these
> cells so I cannot do it manually. Any suggestions?

Reply to this Message

Marcelo - 24 Aug 2006 19:36 GMT

hi,

=((day(a1)*24+hour(a1))*3600)+(minute(a1)*60)+second(a1)

hth

Signature

regards from Brazil
Thanks in advance for your feedback.
Marcelo

Reply to this Message