Calculating Hours, Minutes and Seconds

[Lotling]

I have a list of telephone calls exported from another database into CSV then
into Excel (original db only allows export into CSV and cannot be changed).
These are in hours minutes and seconds and could relate to around 500 entries.
I want to calculate the total duration of all calls in hours, minutes &
seconds
A1 = 00:05:03
A2 = 01:18:52
A3 = 00:45:32
TOTAL 02:08:87
I have changed the column to hh:mm:ss and get 00:00:00 as the total. I also
changed the column to text and divided it by 24/60/60 which just gave an
error of VALUE.
Incidentally, if I manually type time in and add it up, I get the correct
time.
Any ideas?

 

[Mike H]

Try this

=SUMPRODUCT(TRIM(A1:A3)+0)

Apply the appropriate time format

[hh]:mm:ss

Mike

 

[Pete_UK]

Those times have been imported as text values, so you could try this:

highlight the column, click on Data | Text-to-values and then click
Finish on the first panel of the wizard.

You might also have some non-breaking space characters (code 160) in
there. Highlight the column, CTRL-H (for Find/Replace), then:

Find what: Alt-0160
Replace with: leave blank
Click Replace All,

where Alt-0160 means hold down the Alt key while you type 0160 on the
numeric keypad.

Hope this helps.

Pete

 

[Mike H]

BTW the correct sum of your sample data is

02:09:27

Mike

Try this

=SUMPRODUCT(TRIM(A1:A3)+0)

Apply the appropriate time format

[hh]:mm:ss

Mike

I have a list of telephone calls exported from another database into CSV then
into Excel (original db only allows export into CSV and cannot be changed).
These are in hours minutes and seconds and could relate to around 500 entries.
I want to calculate the total duration of all calls in hours, minutes &
seconds
A1 = 00:05:03
A2 = 01:18:52
A3 = 00:45:32
TOTAL 02:08:87
I have changed the column to hh:mm:ss and get 00:00:00 as the total. I also
changed the column to text and divided it by 24/60/60 which just gave an
error of VALUE.
Incidentally, if I manually type time in and add it up, I get the correct
time.
Any ideas?

 

[Lotling]

Thanks Mike,

However it did not work, changed the formatting to hh:mm:ss and applied the
formula but got a result of FALSE.

(as for my calculation of the time it just feels like a long time to the
weekend, that is my excuse anyway)

BTW the correct sum of your sample data is

02:09:27

Mike

Try this

=SUMPRODUCT(TRIM(A1:A3)+0)

Apply the appropriate time format

[hh]:mm:ss

Mike

I have a list of telephone calls exported from another database into CSV then
into Excel (original db only allows export into CSV and cannot be changed).
These are in hours minutes and seconds and could relate to around 500 entries.
I want to calculate the total duration of all calls in hours, minutes &
seconds
A1 = 00:05:03
A2 = 01:18:52
A3 = 00:45:32
TOTAL 02:08:87
I have changed the column to hh:mm:ss and get 00:00:00 as the total. I also
changed the column to text and divided it by 24/60/60 which just gave an
error of VALUE.
Incidentally, if I manually type time in and add it up, I get the correct
time.
Any ideas?

 

[Mike H]

Hi,

Leave the cells with the times in formatted as they are which is probably
text and possibly with spaces. Don't re-type my formula, copy it from this
window and paste it into the formula bar.

Format my formula as [hh]:mm:ss

=SUMPRODUCT(TRIM(A1:A3)+0)


Mike

Thanks Mike,

However it did not work, changed the formatting to hh:mm:ss and applied the
formula but got a result of FALSE.

(as for my calculation of the time it just feels like a long time to the
weekend, that is my excuse anyway)

BTW the correct sum of your sample data is

02:09:27

Mike

Try this

=SUMPRODUCT(TRIM(A1:A3)+0)

Apply the appropriate time format

[hh]:mm:ss

Mike

:

I have a list of telephone calls exported from another database into CSV then
into Excel (original db only allows export into CSV and cannot be changed).
These are in hours minutes and seconds and could relate to around 500 entries.
I want to calculate the total duration of all calls in hours, minutes &
seconds
A1 = 00:05:03
A2 = 01:18:52
A3 = 00:45:32
TOTAL 02:08:87
I have changed the column to hh:mm:ss and get 00:00:00 as the total. I also
changed the column to text and divided it by 24/60/60 which just gave an
error of VALUE.
Incidentally, if I manually type time in and add it up, I get the correct
time.
Any ideas?

 

[Lotling]

Thank You Thank you Thank You

The default was general but changing to text then just the cell with the
calculation works perfectly.

Hi,

Leave the cells with the times in formatted as they are which is probably
text and possibly with spaces. Don't re-type my formula, copy it from this
window and paste it into the formula bar.

Format my formula as [hh]:mm:ss

=SUMPRODUCT(TRIM(A1:A3)+0)


Mike

Thanks Mike,

However it did not work, changed the formatting to hh:mm:ss and applied the
formula but got a result of FALSE.

(as for my calculation of the time it just feels like a long time to the
weekend, that is my excuse anyway)

BTW the correct sum of your sample data is

02:09:27

Mike

:

Try this

=SUMPRODUCT(TRIM(A1:A3)+0)

Apply the appropriate time format

[hh]:mm:ss

Mike

:

I have a list of telephone calls exported from another database into CSV then
into Excel (original db only allows export into CSV and cannot be changed).
These are in hours minutes and seconds and could relate to around 500 entries.
I want to calculate the total duration of all calls in hours, minutes &
seconds
A1 = 00:05:03
A2 = 01:18:52
A3 = 00:45:32
TOTAL 02:08:87
I have changed the column to hh:mm:ss and get 00:00:00 as the total. I also
changed the column to text and divided it by 24/60/60 which just gave an
error of VALUE.
Incidentally, if I manually type time in and add it up, I get the correct
time.
Any ideas?