Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 30
Default Adding up time values

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.
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2
Default Adding up time values

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)
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 30
Default Adding up time values

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)

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default Adding up time values

How about:

=SUM(A1:A14+0)

....confirmed with CTRL+SHIFT+ENTER

Change range to suit.
  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,651
Default Adding up time values

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.





  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 4
Default Adding up time values


"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


  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 30
Default Adding up time values

nope

"Jon von der Heyden" wrote:

How about:

=SUM(A1:A14+0)

....confirmed with CTRL+SHIFT+ENTER

Change range to suit.

  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 5,651
Default Adding up time values

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
  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 30
Default Adding up time values

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



  #10   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 30
Default Adding up time values



"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



  #11   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 5,651
Default Adding up time values

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
  #12   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,651
Default Adding up time values

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



  #13   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 30
Default Adding up time values

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

  #14   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 5,651
Default Adding up time values

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
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Adding Time Values in HH:MM Format SiH23 Excel Discussion (Misc queries) 3 October 3rd 08 04:10 PM
Adding Time Values Together Angiewelly Excel Discussion (Misc queries) 2 September 6th 07 11:38 AM
Problem with adding time values Dan Excel Worksheet Functions 4 November 10th 05 05:51 PM
Adding time Values up in Excel John Excel Worksheet Functions 4 September 13th 05 02:55 PM
time format and adding tiome values. dave m Excel Worksheet Functions 3 November 20th 04 08:55 AM


All times are GMT +1. The time now is 10:03 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"