Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

# Thread: Convert Hours, Minutes And Seconds Into Total Seconds

1. I agreed to these rules
Join Date
10th April 2008
Posts
7

## Convert Hours, Minutes And Seconds Into Total Seconds

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?

Excel Video Tutorials / Excel Dashboards Reports

2. ## Re: Convert Hours, Minutes And Seconds Into Total Seconds

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)

3. ## Re: Convert Hours, Minutes And Seconds Into Total Seconds

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.

Excel Video Tutorials / Excel Dashboards Reports

4. ## Re: Convert Hours, Minutes And Seconds Into Total Seconds

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.

5. ## Re: Convert Hours, Minutes And Seconds Into Total Seconds

Tom, has the best solution, IMO

K.I.S.S

6. I agreed to these rules
Join Date
10th April 2008
Posts
7

## Re: Convert Hours, Minutes And Seconds Into Total Seconds

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

Excel Video Tutorials / Excel Dashboards Reports

7. Join Date
27th April 2012
Posts
1

## Re: Convert Hours, Minutes And Seconds Into Total Seconds

Originally Posted by Dave Hawley
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.

Excel Video Tutorials / Excel Dashboards Reports

8. ## Re: Convert Hours, Minutes And Seconds Into Total Seconds

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

There are currently 5 users browsing this thread. (0 members and 5 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•
porno