Summing [h]:mm:ss and/or converting to total seconds

**networkmd** · 06-16-2009, 09:29 AM

All,

I am in need of some help with some time-format stuff.

I am trying to calculate some long-distance costs, and we get billed by the second.

What I have is a call detail report that contains the durations of every single call we've made. In my column J, I have those lengths, with custom formatting of [h]:mm:ss:

J
:12:19
:02:21
48:00:26
:13:02
:37:02
:01:34
:41:55

The fact that Excel is now showing a zero for those calls that lasted less than one hour is killing me and I can't figure out how to get it in there.

In the grand scheme of things, I need to convert each of these times to total seconds for each call. On some websites help, I have tried using '=HOUR(J2)*3600+MINUTE(J2)*60+SECOND(J2) to no avail. Cell J2 would be the one above containing 02:21, but since there is nothing present for the number of hours, it bombs as a bad value.

Interesting enough, if I continue on down and run that formula on J3 which contains an hours value, (48:00:26), it does do a calculation, but I've no idea how it came up with what it does....it comes back with 2246400. If you do the math on a calculator, (48*3600)+(0*60)+26, you'll get 172826 seconds, which is correct.

Any ideas for how I can get those call durations converted to seconds for each call?

Thanks in advance!

**kailashnj** · 06-16-2009, 09:41 AM

Hi,

You need to convert the hr format into decimal format for any summing. For this you need to multiply the number with 1440 (to convert into seconds format).

For e.g :- 48:00:00 would be shown as 2880.0 (this is down to seconds level) in decimal format.

Steps to follow:

1. Add all your time format numbers in one coloum e.g A1, A2, A3 and so on
2. In the coloumn next to this use the following formula "=A1*1440" (without the quotes)
3. Drag this formula till all the data that needs to be converted is cleared.
4. Select the entire B coloumn and convert the cell formatting to number. (format Cells --> number formatting).

This will give you the correct decimal answer. i.e 48:00:00 will show as 2880.00 and time in seconds like 0:0:45 will show as 0.75

This will help. Now to convert back to time format - instead of multiply use division.

I have added a sample excel file for your better understanding.
Hope this helps.

Kailash

**networkmd** · 06-16-2009, 09:53 AM

Thanks for the reply. It worked for the column that had 48 hours, but not for the others, similar to what I've been experiencing before - it can't handle the missing value in the hours, and can't just assume it's zero:

J is my original data, K is =J2*1440

Column J Column K
:12:19 #VALUE!
:02:21 #VALUE!
48:00:26 2880.433333
:13:02 #VALUE!
:37:02 #VALUE!
:01:34 #VALUE!

Edit: Sorry for the lost formatting, hopefully you can ready this.

**martindwilson** · 06-16-2009, 09:54 AM

looks like the :00:30 are text so
=IF(LEFT(A1,1)=":",TEXT("00"&A1,"hh:mm:ss"),A1)*86400
dragged down will give seconds cells formatted general
change a1 to j1 as required

**daddylonglegs** · 06-16-2009, 09:54 AM

You can convert to seconds with this formula in K2 copied down

=(0&J2)*86400

**networkmd** · 06-16-2009, 09:57 AM

I just saw the example you attached. So my real issue is those missing zeros. Any idea how to get them quickly added in, to about 25,000 rows of data? Adding them manually is out of the question.