Cannot SUM times

[Martyn B Tindall]

I have used my phone company's "Export to Excel" function to download
my itemised telephone bill from their web-site. Each row represents
one phone call. The last two columns are headed "Duration (hh:mm:ss)"
and "Cost (£)".

I can total the whole or portions of the Cost column, which contains a
simple decimal number, without problems, using AutoSum. However, when
I try to use AutoSum on the duration column, AutoSum doesn't know
which cells I'm looking at and just shows =SUM(). If I type in the
cell range, it returns 00:00:00. I can't use SUM at all on these
figures, though a simple =E2+E3+E4 (etc.) returns the correct answer.

If I copy and paste a part of the column to a blank column, the same
happens; but if I type the numbers in myself, they total correctly,
though the cells look identical to the originals. I've formatted the
columns concerned "Custom; hh:mm:ss", though the duration column was
formatted "General" in the first instance.

The phone company's customer services acknowledge the problem, but
can't explain it or suggest a solution. Surely there must be a way
round this?

TIA,

 

[Aladin Akyurek]

[...]

I can total the whole or portions of the Cost column, which contains a
simple decimal number, without problems, using AutoSum. However, when
I try to use AutoSum on the duration column, AutoSum doesn't know
which cells I'm looking at and just shows =SUM(). If I type in the
cell range, it returns 00:00:00. I can't use SUM at all on these
figures, though a simple =E2+E3+E4 (etc.) returns the correct answer.

If I copy and paste a part of the column to a blank column, the same
happens; but if I type the numbers in myself, they total correctly,
though the cells look identical to the originals. I've formatted the
columns concerned "Custom; hh:mm:ss", though the duration column was
formatted "General" in the first instance.

The phone company's customer services acknowledge the problem, but
can't explain it or suggest a solution. Surely there must be a way
round this?

[...]

Time values are probably text-formatted. In order to make them true time
values...

Select an unused/empty cell.
Run Edit|Copy.
Select the range of text-formatted time values.
Run Edit|Paste Special with Operation set to Add.
Time format the target cells.

 

[Martyn B Tindall]

[...]

I can total the whole or portions of the Cost column, which contains a
simple decimal number, without problems, using AutoSum. However, when
I try to use AutoSum on the duration column, AutoSum doesn't know
which cells I'm looking at and just shows =SUM(). If I type in the
cell range, it returns 00:00:00. I can't use SUM at all on these
figures, though a simple =E2+E3+E4 (etc.) returns the correct answer.

If I copy and paste a part of the column to a blank column, the same
happens; but if I type the numbers in myself, they total correctly,
though the cells look identical to the originals. I've formatted the
columns concerned "Custom; hh:mm:ss", though the duration column was
formatted "General" in the first instance.

The phone company's customer services acknowledge the problem, but
can't explain it or suggest a solution. Surely there must be a way
round this?

[...]

Time values are probably text-formatted. In order to make them true time
values...

Select an unused/empty cell.
Run Edit|Copy.
Select the range of text-formatted time values.
Run Edit|Paste Special with Operation set to Add.
Time format the target cells.

Worked like a charm - thanks very much!

Do you think I'll be able to automate this process with a macro
(though I've no experience of macros), bearing in mind that the number
of rows will be different each month?

 

[Aladin Akyurek]

You could record a macro that you can run when needed. An alternative would
be to run ASAP Utilities (a free add-in).

[...]

I can total the whole or portions of the Cost column, which contains a
simple decimal number, without problems, using AutoSum. However, when
I try to use AutoSum on the duration column, AutoSum doesn't know
which cells I'm looking at and just shows =SUM(). If I type in the
cell range, it returns 00:00:00. I can't use SUM at all on these
figures, though a simple =E2+E3+E4 (etc.) returns the correct answer.

If I copy and paste a part of the column to a blank column, the same
happens; but if I type the numbers in myself, they total correctly,
though the cells look identical to the originals. I've formatted the
columns concerned "Custom; hh:mm:ss", though the duration column was
formatted "General" in the first instance.

The phone company's customer services acknowledge the problem, but
can't explain it or suggest a solution. Surely there must be a way
round this?

[...]

Time values are probably text-formatted. In order to make them true time
values...

Select an unused/empty cell.
Run Edit|Copy.
Select the range of text-formatted time values.
Run Edit|Paste Special with Operation set to Add.
Time format the target cells.

Worked like a charm - thanks very much!

Do you think I'll be able to automate this process with a macro
(though I've no experience of macros), bearing in mind that the number
of rows will be different each month?


--
Martyn Tindall
Leeds, England, United Kingdom

(To reply, please remove "spamfree.".)

 

[Martyn B Tindall]

You could record a macro that you can run when needed. An alternative would be to run ASAP Utilities (a free add-in).

Thanks for all your help!