problem with summing
 

Hi everyone,

I'm having problems summing up a range of values. It's supposed to be time
but because the range of data was copied over from another program, 30
seconds would be displayed as ":30".

I've tried auto adding a "0" or "00" in front of it and changing the formats
to mm:ss but the sum of the range of figures still comes up as 00:00. Anyone
knows how I can go about summing up the total amount of time? Thanks!

Dear CS78

You must enter time in the correct format with a colon to seperate the
seconds from the minutes from the hours. To do this specifically for
seconds means that you have to enter 00:00:15 and ensure that you
format it as hh:mm:ss. You can make things a bit easier by pretending
the seconds are minutes and reduce the number of 00:'s you will have to
enter, but once you've you done the formatting and entered seconds
correctly, the rest of it will be easy

Cheers
Jon


--
Jon Quixley
------------------------------------------------------------------------
Jon Quixley's Profile: http://www.excelforum.com/member.php...o&userid=25803
View this thread: http://www.excelforum.com/showthread...hreadid=476160

=SUMPRODUCT(--(LEFT("00:00:00",8-LEN(A1:A10))&A1:A10))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"cs78 via OfficeKB.com" <u14879@uwe wrote in message
news:55d04ccffbec0@uwe...
Hi everyone,

I'm having problems summing up a range of values. It's supposed to be time
but because the range of data was copied over from another program, 30
seconds would be displayed as ":30".

I've tried auto adding a "0" or "00" in front of it and changing the
formats
to mm:ss but the sum of the range of figures still comes up as 00:00.
Anyone
knows how I can go about summing up the total amount of time? Thanks!

It worked perfectly, thanks so much for your help!

Bob Phillips wrote:
=SUMPRODUCT(--(LEFT("00:00:00",8-LEN(A1:A10))&A1:A10))

Hi everyone,

[quoted text clipped - 5 lines]
to mm:ss but the sum of the range of figures still comes up as 00:00. Anyone
knows how I can go about summing up the total amount of time? Thanks!

Hi

To convert your data, assuming the value is in A1
=--("00:00"&A1)
will convert it to a format that Excel understands as time.
You need to choose FormatCellsNumberCustom and choose hh:mm:ss
To sum the times just use =SUM(A1:A100) but format the cell as
[hh]:mm:ss to allow it to sum past 24 hours if necessary.

Regards

Roger Govier



cs78 via OfficeKB.com wrote:

Hi everyone,

I'm having problems summing up a range of values. It's supposed to be time
but because the range of data was copied over from another program, 30
seconds would be displayed as ":30".

I've tried auto adding a "0" or "00" in front of it and changing the formats
to mm:ss but the sum of the range of figures still comes up as 00:00. Anyone
knows how I can go about summing up the total amount of time? Thanks!