Adding custon time data

[ma365]

Hi,

I have a column with time data in it. The format is HH:MM:SS.00

I'd like to sum value of all the data in the column.

If I manually add data in the column Excel provides the correct result.

e.g =(A1+A2+A3) This works.

If I use the built in SUM function it does not work.

e.g =SUM(A1:A3) This always gives 00:00:00.0 regardless of the values in the rows.

There are thousands of rows in the column making it inpractical to manually type =(a1+a2+a3.......+a21003)

Does anyone know if this is a limation of the SUM function for custom formatting or am I doing something wrong?

Also can anyone advise how I could automate the addition of adding the columns if I have to, save me typing out =(a1+a2+a3.......+a21003) etc..

Thanks in advance
ma365.

 

[T. Valko]

Hi,

I have a column with time data in it. The format is HH:MM:SS.00

I'd like to sum value of all the data in the column.

If I manually add data in the column Excel provides the correct result.

e.g =(A1+A2+A3) This works.

If I use the built in SUM function it does not work.

e.g =SUM(A1:A3) This always gives 00:00:00.0 regardless of the values in the rows.

There are thousands of rows in the column making it inpractical to manually type =(a1+a2+a3.......+a21003)

Does anyone know if this is a limation of the SUM function for custom formatting or am I doing something wrong?

Also can anyone advise how I could automate the addition of adding the columns if I have to, save me typing out =(a1+a2+a3.......+a21003) etc..

Thanks in advance
ma365.

It sounds like your time entries might be TEXT entries.

When you use the explicit math operation of addition:

=A1+A2

This coerces the text entry into a true numeric time value. The SUM function does not perform this conversion.

So, you need to try to convert these text values to true numeric time values.

Try this...

• Select an empty cell
• Copy that empty cell: Right Click>Copy
• Select the range of cells that contain the time entries
• Right Click>Paste Special>Add>OK

You might have to reformat the cells as Time