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.

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)

It didn't work

" wrote:

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)

How about:

=SUM(A1:A14+0)

....confirmed with CTRL+SHIFT+ENTER

Change range to suit.

If you've rechecked that you've got times and not text in your cells, and
that your formula is looking at the right cells, I would suggest that you
upload your file to somewhere like http://www.savefile.com/ and let us know
the url.
--
David Biddulph

"GAIDEN" wrote in message
...
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.

"GAIDEN" wrote in message
...
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

nope

"Jon von der Heyden" wrote:

How about:

=SUM(A1:A14+0)

....confirmed with CTRL+SHIFT+ENTER

Change range to suit.

On Sun, 16 Nov 2008 02:23:00 -0800, GAIDEN
wrote:

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

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

={IF(I160,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.


"Peter Andrews" wrote:


"GAIDEN" wrote in message
...
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

"Ron Rosenfeld" wrote:

On Sun, 16 Nov 2008 02:23:00 -0800, GAIDEN
wrote:

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."

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


What was the format of the cell BEFORE any data was entered?

h:mm:ss

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


TRUE



--ron

On Sun, 16 Nov 2008 13:19:01 -0800, GAIDEN
wrote:



"Ron Rosenfeld" wrote:

On Sun, 16 Nov 2008 02:23:00 -0800, GAIDEN
wrote:

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."

i went to custom format


How did you try to format this?

see above


What does "without luck" mean in this context?

without success

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


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

What document type?



What was the format of the cell BEFORE any data was entered?

h:mm:ss

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


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

So that confirms that you've got text instead of real times, so your sum
formula won't work.
You may want to look for spaces or other non-printing characters, & get rid
of them.
--
David Biddulph

"GAIDEN" wrote in message
...
"Ron Rosenfeld" wrote:
....
What do get if you enter into some cell =ISTEXT(cell_ref) where cell_ref
refers
to a cell containing a time value.

TRUE

that last bit did it. thanks for the help

"Ron Rosenfeld" wrote:

On Sun, 16 Nov 2008 13:19:01 -0800, GAIDEN
wrote:



"Ron Rosenfeld" wrote:

On Sun, 16 Nov 2008 02:23:00 -0800, GAIDEN
wrote:

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."

i went to custom format


How did you try to format this?

see above


What does "without luck" mean in this context?

without success

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


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

What document type?



What was the format of the cell BEFORE any data was entered?

h:mm:ss

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


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

On Sun, 16 Nov 2008 15:44:01 -0800, GAIDEN
wrote:

that last bit did it. thanks for the help

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