Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

# Thread: Convert hh:mm:ss to seconds

1. I agreed to these rules
Join Date
11th March 2005
Posts
4

## Convert hh:mm:ss to seconds

I have a cell with hh:mm:ss formatted values of say 2:02:48, but need to run some calculations. I want to convert this to the just the number of seconds .. in this case 7368 seconds, but can't see how to do it. Changing the format of the cell to just ss returns 48.

I'm sure this is really simple and I'm being a bit thick.

Excel Video Tutorials / Excel Dashboards Reports

2. ## Re: Convert hh:mm:ss to seconds

Excel stores dates and times as numbers, with the integers representing days (since 1 january 1900). The decimal part is the fraction of a day.

If your cell has only hh:mm:ss with no day part, (Excel will treat it as that time on 1 January 1900), simply multiply the cell by the number of seconds in a day -- 24*60*60)

EDIT: If your cell might also have a non-zero date component, just to be safe you could use:

=MOD(A1,1)*24*60*60

where A1 is assumed to have your orginal hh:mm:ss data.
Last edited by thomach; May 16th, 2005 at 22:54.

3. I agreed to these rules
Join Date
11th March 2005
Posts
4

## Re: Convert hh:mm:ss to seconds

Thomach,

Excellent - exactly what was required. Many thanks for the quick response. Thanks too to OzGrid

Excel Video Tutorials / Excel Dashboards Reports

4. I agreed to these rules
Join Date
27th September 2013
Posts
1

## Re: Convert hh:mm:ss to seconds

Originally Posted by dawkinj
I have a cell with hh:mm:ss formatted values of say 2:02:48, but need to run some calculations. I want to convert this to the just the number of seconds .. in this case 7368 seconds, but can't see how to do it. Changing the format of the cell to just ss returns 48.

I'm sure this is really simple and I'm being a bit thick.
The easiest and cleanest way to do this is simply change the cells format to:
[s]

placing an indicator inside square brackets removes the limitation to its normal reset value. Ie seconds = 60, minutes = 60, hours = 24.

the format [h]:mm:ss will stop that anoying display with the number of days if the duration is greater than 24 hours.

Hope this helps

Excel Video Tutorials / Excel Dashboards Reports

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 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