adding time issue

[lezawang]

Hi
I am trying to add time but it does not work. Please see the excel file in the link.

in cell P = SUM(C15:O15)

But it is giving me 0

I tried to format to [h]:mm but did not help. Not sure what else I could have done. I also multiple the SUM(C15:O15) * 24 but still 0:00:00 shown?! I am copying the row I want to add here as well


[TABLE="class: grid, width: 960"]
<tbody>[TR]
[TD="width: 64"]Total[/TD]
[TD="width: 64"]40:03:28[/TD]
[TD="width: 64"]44:38:40[/TD]
[TD="width: 64"]06:55:37[/TD]
[TD="width: 64"]21:05:50[/TD]
[TD="width: 64"]03:04:55[/TD]
[TD="width: 64"]01:42:12[/TD]
[TD="width: 64"]01:31:29[/TD]
[TD="width: 64"]14:51:06[/TD]
[TD="width: 64"]11:01:44[/TD]
[TD="width: 64"]22:53:40[/TD]
[TD="width: 64"]49:56:10[/TD]
[TD="width: 64"]103:31:49[/TD]
[TD="width: 64"]95:24:02[/TD]
[TD="width: 64, align: right"]0:00:00[/TD]
[/TR]
</tbody>[/TABLE]

Thank you so much.

https://drive.google.com/file/d/1Tn1e73RuyRh4W4Y250VX5SXnr-HAeiHA/view?usp=sharing

 

[Fluff]

They are all text values.

 

[lezawang]

Thank you so much Fluff. What I did, I changed all cells C16:O16 to Time (highlight then General-->Time) then I changed P16 format to [h]:mm:ss
then I got P16 = 49:56:10 (better than 0 but It can not be the right answers since I have cells which has value of 103:31:49 .. the total must be greater than 49:56:10. Thank you very much.

 

[Fluff]

Changing the format of the cell does not change the underlying value.
You will need to convert all those values to numbers before you can add them.

 

[lezawang]

Thank you so much again. So if i want to add these 2 added time [TABLE="class: cms_table_grid, width: 960"]
<tbody>[TR]
[TD="width: 64"]40:03:28[/TD]
[TD="width: 64"]44:38:40[/TD]
[/TR]
</tbody>[/TABLE]

these now are not time, they are text, right? and I need to change them to number before I can do

=Sum(A1,A2)

Right?

I convert them to number but still the total is 0 ? and when I convert them to number, they stayed like that 40:03:28 and 44:38:40

 

[offthelip]

have you tried :
=Timevalue(A1) + Timevalue(A2)

 

[MARK858]

@lezawang, while what offthelip posted should work I personally would convert it to numbers as you have tried to make it easier/more efficient in the future (or at least have another converted table). How did you try to convert the table to numbers?

 

[lezawang]

I highlighted the Total row except cell P16 and went to Format Cells window -->Number and clicked ok.
Nothing changed. for example 40:03:28 stayed as it is. Then the total still 0. By accident I doubled clicked on one cell and nothing happened. Then I clicked outside that cell and then the value of the cell changed to number, for example cell C16 = 1.67. Why is that? Why I have to double click and then click outside then the cell value change or updated to number?

Then I tried Offthelip idea and used =timevalue() function but the first value gave me error message #value but the reset were converted to number.

So I went back and convert them to number and changed cell P16 which has the total to number as well (Format cells -->number) then I got 17.36 . Do I need to multiple that number by 24? Would that correct value /answer from your point of view?

I really appreciate the help very much. Thank you all

@lezawang, while what offthelip posted should work I personally would convert it to numbers as you have tried to make it easier/more efficient in the future (or at least have another converted table). How did you try to convert the table to numbers?

 

[MARK858]

As Fluff pointed out changing the format does not change the underlying value, it just changes its appearance (imagine looking at an image through a filter, the original image doesn't change but how you perceive it does). If you don't mind working with a separate table it is probably easiest to just reference the cells with a formula of the cell reference +0 and custom format the cells as [hh]:mm:ss i.e.

Excel 2016 (Windows) 64 bit
	B	C	D	E	F	G	H	I	J	K	L	M	N	O
2	Month	overtime1	overtime2	overtime3	overtime4	overtime5	overtime6	overtime7	overtime8	overtime9	overtime10	overtime11	overtime12	overtime13
3	JAN	02:05:42			01:35:56	00:07:17							07:30:30	06:33:33
4	FEB	01:57:24			02:40:44	00:01:26		00:06:08					07:19:54	09:16:22
5	MAR	02:45:55			02:46:47	02:07:54							12:11:56	12:20:01
6	APR	24:48:53	19:56:50	05:12:23	04:00:25	00:06:36	01:07:00	00:25:35					06:36:53	11:18:38
7	MAY	04:14:15	15:15:33	00:49:24	03:15:18	00:28:34		00:02:38					06:44:49	08:26:00
8	JUN	01:13:55	04:43:37	00:17:23	02:12:52	00:13:08	00:17:42	00:28:46	05:56:13	06:42:38	09:52:26	23:27:29	13:15:58	08:41:48
9	JUL	00:19:37	00:49:47	00:11:02	01:25:45		00:07:43	00:02:58	06:07:22	02:44:48	10:18:06	20:18:21	11:16:34	06:46:22
10	AUG	00:28:04	01:10:38	00:11:37	00:49:18		00:05:54	00:16:12	01:24:53	00:55:50	01:45:14	03:11:03	07:30:55	06:25:28
11	SEP	00:19:04	01:00:05	00:02:11	00:35:51		00:03:53	00:08:47	00:39:35	00:06:47	00:19:39	01:00:19	07:42:45	07:15:14
12	OCT	00:07:50	00:51:07	00:07:04	00:43:52				00:32:35	00:09:38	00:08:17	00:32:07	08:26:28	05:39:39
13	NOV	00:06:43	00:33:39	00:04:33	00:34:12				00:06:45	00:19:50	00:26:34	01:21:36	07:44:18	06:52:32
14	DEC	01:36:06	00:17:24		00:24:50			00:00:25	00:03:43	00:02:13	00:03:24	00:05:15	07:10:49	05:48:25
15	Total	40:03:28	44:38:40	06:55:37	21:05:50	03:04:55	01:42:12	01:31:29	14:51:06	11:01:44	22:53:40	49:56:10	103:31:49	95:24:02
16
17	Month	overtime1	overtime2	overtime3	overtime4	overtime5	overtime6	overtime7	overtime8	overtime9	overtime10	overtime11	overtime12	overtime13
18	JAN	02:05:42	00:00:00	00:00:00	01:35:56	00:07:17	00:00:00	00:00:00	00:00:00	00:00:00	00:00:00	00:00:00	07:30:30	06:33:33
19	FEB	01:57:24	00:00:00	00:00:00	02:40:44	00:01:26	00:00:00	00:06:08	00:00:00	00:00:00	00:00:00	00:00:00	07:19:54	09:16:22
20	MAR	02:45:55	00:00:00	00:00:00	02:46:47	02:07:54	00:00:00	00:00:00	00:00:00	00:00:00	00:00:00	00:00:00	12:11:56	12:20:01
21	APR	24:48:53	19:56:50	05:12:23	04:00:25	00:06:36	01:07:00	00:25:35	00:00:00	00:00:00	00:00:00	00:00:00	06:36:53	11:18:38
22	MAY	04:14:15	15:15:33	00:49:24	03:15:18	00:28:34	00:00:00	00:02:38	00:00:00	00:00:00	00:00:00	00:00:00	06:44:49	08:26:00
23	JUN	01:13:55	04:43:37	00:17:23	02:12:52	00:13:08	00:17:42	00:28:46	05:56:13	06:42:38	09:52:26	23:27:29	13:15:58	08:41:48
24	JUL	00:19:37	00:49:47	00:11:02	01:25:45	00:00:00	00:07:43	00:02:58	06:07:22	02:44:48	10:18:06	20:18:21	11:16:34	06:46:22
25	AUG	00:28:04	01:10:38	00:11:37	00:49:18	00:00:00	00:05:54	00:16:12	01:24:53	00:55:50	01:45:14	03:11:03	07:30:55	06:25:28
26	SEP	00:19:04	01:00:05	00:02:11	00:35:51	00:00:00	00:03:53	00:08:47	00:39:35	00:06:47	00:19:39	01:00:19	07:42:45	07:15:14
27	OCT	00:07:50	00:51:07	00:07:04	00:43:52	00:00:00	00:00:00	00:00:00	00:32:35	00:09:38	00:08:17	00:32:07	08:26:28	05:39:39
28	NOV	00:06:43	00:33:39	00:04:33	00:34:12	00:00:00	00:00:00	00:00:00	00:06:45	00:19:50	00:26:34	01:21:36	07:44:18	06:52:32
29	DEC	01:36:06	00:17:24	00:00:00	00:24:50	00:00:00	00:00:00	00:00:25	00:03:43	00:02:13	00:03:24	00:05:15	07:10:49	05:48:25
30	Total	40:03:28	44:38:40	06:55:37	21:05:50	03:04:55	01:42:12	01:31:29	14:51:06	11:01:44	22:53:40	49:56:10	103:31:49	95:24:02
OT

Cell Formulas
Range	Formula
B17	=B2
B18	=B3
C17	=C2
C18	=C3+0
C30	=SUM(C18:C29)

 

[Rick Rothstein]

Does this formula work for you (using the original range you posted with your values remaining as Text)...

=SUMPRODUCT(C15:O15+0)

Format the cell you put this in using [h]:mm:ss