Adding up time values

[GAIDEN]

work hours
04:31:26
01:14:19
01:57:24
02:27:32
03:01:01
02:42:15
02:34:16
02:11:15
02:17:57
00:24:35
02:03:44
01:10:22
03:23:12
10:09:12

I'm trying to add these work hours together and all i keep getting is
0:00:00. I've also tried formatting the work times (Example: 04:31:26 to
4.52 hours) without luck.

 

[jon]

Hi Gaiden

First highlight the column that houses the dates and then on the menu
bar go to Data > Text to Columns. Choose "delimited" and then hit
finish.

This should force the values into a valid date / number format.

Then proceed to add as normal, e.g. =SUM(A1:A20)

 

[GAIDEN]

It didn't work

 

[Jon von der Heyden]

How about:

=SUM(A1:A14+0)

....confirmed with CTRL+SHIFT+ENTER

Change range to suit.

 

[Peter Andrews]

work hours
04:31:26
01:14:19
01:57:24
02:27:32
03:01:01
02:42:15
02:34:16
02:11:15
02:17:57
00:24:35
02:03:44
01:10:22
03:23:12
10:09:12

I'm trying to add these work hours together and all i keep getting is
0:00:00. I've also tried formatting the work times (Example: 04:31:26 to
4.52 hours) without luck.

Adds to 40:08:30 for me... make sure that all the cells are custom
formatted as hh:mm:ss

Peter

 

[GAIDEN]

nope

 

[Ron Rosenfeld]

work hours
04:31:26
01:14:19
01:57:24
02:27:32
03:01:01
02:42:15
02:34:16
02:11:15
02:17:57
00:24:35
02:03:44
01:10:22
03:23:12
10:09:12

I'm trying to add these work hours together and all i keep getting is
0:00:00. I've also tried formatting the work times (Example: 04:31:26 to
4.52 hours) without luck.

What exactly do you mean when you write "tried formatting the work times
(Example: 04:31:26 to 4.52 hours) without luck."

How did you try to format this?
What does "without luck" mean in this context?

Most likely your values are text representations and not real numbers.

How did the values get into the worksheet?
What was the format of the cell BEFORE any data was entered?

What do get if you enter into some cell =ISTEXT(cell_ref) where cell_ref refers
to a cell containing a time value.

--ron

 

[GAIDEN]

the formula used to display the work hour values from another worksheet
(CR1203) is

={IF(I16>0,INDEX('CR1203'!$I$8:$I$2001,MATCH(F16&"",LEFT('CR1203'!$C$8:$C$2001,4),0)),"")}

I don't know if it's this formula that won't allow me to use sum() to add
the values or if it's the way the data was downloaded on to the sheet named
CR1203 (which is in the same format as below). If I break up the way it's
added (i.e. A1+A2+etc), it works. But if there are blank cells in between
the values,

Column A
Row 1 04:31:26
Row 2 01:14:19
Row 3
Row 4 01:57:24

it will not add them up.

 

[GAIDEN]

What exactly do you mean when you write "tried formatting the work times
(Example: 04:31:26 to 4.52 hours) without luck."

i went to custom format

How did you try to format this?

see above

What does "without luck" mean in this context?

without success

Most likely your values are text representations and not real numbers.

How did the values get into the worksheet?

downloaded in excel format from a report that calculates work hours

 

[Ron Rosenfeld]

i went to custom format



see above



without success

Does that mean that nothing changed, or that you didn't understand the change
that occurred?

downloaded in excel format from a report that calculates work hours

What document type?

TRUE

It is clear that Excel is seeing your values as TEXT, which was my initial
supposition. Your initial tries at converting them to values did not work,
according to what you've posted.

Especially if the original document (report) is an HTML document, it is
probably that your values include a nbsp character.

Try this:

In an adjacent column, to the first entry, enter the following:

=--SUBSTITUTE(TRIM(cell_ref),CHAR(160),"")

Then fill down as far as necessary.

Then try to SUM this adjacent column.
--ron

 

[GAIDEN]

that last bit did it. thanks for the help

Does that mean that nothing changed, or that you didn't understand the change
that occurred?


What document type?


It is clear that Excel is seeing your values as TEXT, which was my initial
supposition. Your initial tries at converting them to values did not work,
according to what you've posted.

Especially if the original document (report) is an HTML document, it is
probably that your values include a nbsp character.

Try this:

In an adjacent column, to the first entry, enter the following:

=--SUBSTITUTE(TRIM(cell_ref),CHAR(160),"")

Then fill down as far as necessary.

Then try to SUM this adjacent column.
--ron

 

[Ron Rosenfeld]

that last bit did it. thanks for the help

You're welcome. Thanks for the feedback.
--ron