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

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

0:00:00

<tbody>
</tbody>

Thank you so much.

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

 

[lezawang]

Thank you so much. Going to do it now and will let you know the result. Thanks again

 

[MARK858]

Ricks suggestion is good if you can't/don't want to have a separate table but my reason for this post is if you are working with the separate table idea then once you have put in the formulas then copy/paste as values to save unnecessary calculations and just keep the simple sum formulas as formulas.
Sorry it was too late to re-edit my original post.

 

[lezawang]

Yes, that is amazing. Thank you very much. But how come it did work with sumproduct() ? also can I ask you why 0 is added please. Thanks a lot once again.

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

 

[lezawang]

It is ok thank you so much. I will try it as well to see if I can learn from it. Thank you so much.

Ricks suggestion is good if you can't/don't want to have a separate table but my reason for this post is if you are working with the separate table idea then once you have put in the formulas then copy/paste as values to save unnecessary calculations and just keep the simple sum formulas as formulas.
Sorry it was too late to re-edit my original post.

 

[Rick Rothstein]

also can I ask you why 0 is added please

The SUMPRODUCT function evaluates each cell of a range one cell at a time (you could also use SUM but then you would have to commit it with CTRL+SHIFT+ENTER) before performing the summation... adding 0 means 0 is added to and evaluated for each cell in the range. Time values in Excel are floating point numbers, but your time values are Text. Whenever you perform a mathematical operation on a Text value whose underlying value is numerical, Excel converts that Text number to a real number in order to be able to perform the mathematical operation. So, adding 0 to your Text time values converts them to real time values (the underlying floating point numbers) and then adds 0 to it (adding 0 to a number does not change it value)... that means each cell's Text time value has 0 added to it which makes each cell's value a real time value which SUMPRODUCT than sums up. The custom format then displays the floating point number to a familiar looking time value.

 

[MARK858]

It is ok thank you so much. I will try it as well to see if I can learn from it. Thank you so much.

Your welcome, I will let Rick explain how sumproduct works because of its use of arrays of True and False (although it is worth Googling it as there are some good explanations out there on the benefits of sumproduct). The +0 we both use just converts True and False to 1 and 0 so you can use them in calculations and changes numbers stored as text to real numbers.
Any arithmetical sign has the same effect which is why you will often see -- being used i.e. =SUMPRODUCT(--C15:O15) or =SUMPRODUCT(C15:O15*1).

Oops posted the same time as Rick, I won't delete the text here as I don't think anything in it detracts from what Rick has posted.

 

[lezawang]

Thank you all indeed. I will read all solution and google sumproduct as Mark suggested. I will try all solution to understand what is going on. Thank once again. Really appreciate.

 

[lezawang]

Thank you. I think I got it. I did exactly what you told me to do. Then I got similar table
The Total row, I created =SUM(C18:C29) in C30 then used auto fill to fill D30, E30, F30 etc until O30
Then in P30 I wrote =SUM(C30:O30).
With that I got in P30 = 17.3616

Now I can multiple P30 * 24 = 416.6783
Or change the format of P30 to

---

:mm:ss and get 416:40:42

Am I right?
Without all of you help, I wont be able to understand it. Thank you so much

<tbody>
</tbody>

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

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

OT​

Worksheet Formulas Cell Formula B17 =B2 C17 =C2 B18 =B3 C18 =C3+0 C30 =SUM([COLOR=rgb(255]C18:C29[/COLOR]) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>