Convert Hours, Minutes And Seconds Into Total Seconds

[Joey666]

Hi All,

Am stuck trying to figure a formula to convert time on a phone call eg. 01:01:21 into total seconds (3661). Phonecalls will never be more than an hour long but the spreadsheet I will be supplied with (havn't got it yet!) will display them in the 00:00:00 format.

Any Ideas?

[PCI]

Assuming the time is in B3

---

Try this formula. Array formulas will have curly brackets {} emcompassing the ENTIRE formula. You do not add these, you Enter the formula with Ctrl+Shift+Enter and Excel will add them (details: Array formulas). Many more Excel Formulas

	=HOUR(B3)*3600+MINUTE(B3)*60+SECOND(B3)

---

[Kenneth Hobson]

If A1=01:01:21 then =A1*86400 is 3681 seconds. Be sure to format the formula cell as Numeric. The 86400 comes from 3600 seconds/hour times 24 hours per day. Time is in a fraction of a day.

[thomach]

You also could format the cell as [s]

FORMAT > CELLS > CUSTOM > enter [s] in the Type box.

Note: This leaves the underlying data as a fraction of a day, but displays the stored fraction in seconds. If you need the number of seconds to be stored so you can do math with it, the convert as above or incorport the conversion formulas into your math.

[Dave Hawley]

Tom, has the best solution, IMO

K.I.S.S

[Joey666]

thanks millions guys, I can get all methods working so will have to wait and see when the data comes through which is best.

Thanks again

[enisthemenace]

Tom, has the best solution, IMO

K.I.S.S

I'm having problems with this.

Tom's may be the 'best solution' but only for display purposes (I think). Don't try to run downstream formula's, because the results will not be correct.

For example: 2 hours, 43 minutes and 31 seconds (2:43:31) is a total of 9811 seconds. Tom's formatting shortcut will display the correct number in the cell, but that's it.

Do it the long way, and place your answer in A1. Put the formatted shortcut in A2. Then apply this formula in cell A3:

=IF(A1=B1,"Y","N")

The result in A3 will be N.

If this is applied to a list, then the =SUM function is used, the total will not be accurate, unless I'm doing something wrong.

I'm in Excel 2007, so help a brother out if I'm wrong.

Thx.

[PCI]

The unit for time is linked to a number of day
The value for 2:43:31 is 0.113553240740741
It means 0.113553240740741 * 24 * 3600 = 9811 seconds