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!