Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Adding up time values
How about:
=SUM(A1:A14+0) ....confirmed with CTRL+SHIFT+ENTER Change range to suit. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding Time Values in HH:MM Format | Excel Discussion (Misc queries) | |||
Adding Time Values Together | Excel Discussion (Misc queries) | |||
Problem with adding time values | Excel Worksheet Functions | |||
Adding time Values up in Excel | Excel Worksheet Functions | |||
time format and adding tiome values. | Excel Worksheet Functions |