adding time issue

lezawang

Well-known Member
Joined
Mar 27, 2016
Messages
1,805
Office Version
  1. 2016
Platform
  1. Windows
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


Total40:03:2844:38:4006:55:3721:05:5003:04:5501:42:1201:31:2914:51:0611:01:4422:53:4049:56:10103:31:4995:24:020:00:00

<tbody>
</tbody>

Thank you so much.

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

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
They are all text values.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
Thank you so much again. So if i want to add these 2 added time
40:03:2844:38:40

<tbody>
</tbody>

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
 
Upvote 0
@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?
 
Upvote 0
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?
 
Upvote 0
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
BCDEFGHIJKLMNO
2Monthovertime1overtime2overtime3overtime4overtime5overtime6overtime7overtime8overtime9overtime10overtime11overtime12overtime13
3JAN02:05:4201:35:5600:07:1707:30:3006:33:33
4FEB01:57:2402:40:4400:01:2600:06:0807:19:5409:16:22
5MAR02:45:5502:46:4702:07:5412:11:5612:20:01
6APR24:48:5319:56:5005:12:2304:00:2500:06:3601:07:0000:25:3506:36:5311:18:38
7MAY04:14:1515:15:3300:49:2403:15:1800:28:3400:02:3806:44:4908:26:00
8JUN01:13:5504:43:3700:17:2302:12:5200:13:0800:17:4200:28:4605:56:1306:42:3809:52:2623:27:2913:15:5808:41:48
9JUL00:19:3700:49:4700:11:0201:25:4500:07:4300:02:5806:07:2202:44:4810:18:0620:18:2111:16:3406:46:22
10AUG00:28:0401:10:3800:11:3700:49:1800:05:5400:16:1201:24:5300:55:5001:45:1403:11:0307:30:5506:25:28
11SEP00:19:0401:00:0500:02:1100:35:5100:03:5300:08:4700:39:3500:06:4700:19:3901:00:1907:42:4507:15:14
12OCT00:07:5000:51:0700:07:0400:43:5200:32:3500:09:3800:08:1700:32:0708:26:2805:39:39
13NOV00:06:4300:33:3900:04:3300:34:1200:06:4500:19:5000:26:3401:21:3607:44:1806:52:32
14DEC01:36:0600:17:2400:24:5000:00:2500:03:4300:02:1300:03:2400:05:1507:10:4905:48:25
15Total40:03:2844:38:4006:55:3721:05:5003:04:5501:42:1201:31:2914:51:0611:01:4422:53:4049:56:10103:31:4995:24:02
16
17Monthovertime1overtime2overtime3overtime4overtime5overtime6overtime7overtime8overtime9overtime10overtime11overtime12overtime13
18JAN02:05:4200:00:0000:00:0001:35:5600:07:1700:00:0000:00:0000:00:0000:00:0000:00:0000:00:0007:30:3006:33:33
19FEB01:57:2400:00:0000:00:0002:40:4400:01:2600:00:0000:06:0800:00:0000:00:0000:00:0000:00:0007:19:5409:16:22
20MAR02:45:5500:00:0000:00:0002:46:4702:07:5400:00:0000:00:0000:00:0000:00:0000:00:0000:00:0012:11:5612:20:01
21APR24:48:5319:56:5005:12:2304:00:2500:06:3601:07:0000:25:3500:00:0000:00:0000:00:0000:00:0006:36:5311:18:38
22MAY04:14:1515:15:3300:49:2403:15:1800:28:3400:00:0000:02:3800:00:0000:00:0000:00:0000:00:0006:44:4908:26:00
23JUN01:13:5504:43:3700:17:2302:12:5200:13:0800:17:4200:28:4605:56:1306:42:3809:52:2623:27:2913:15:5808:41:48
24JUL00:19:3700:49:4700:11:0201:25:4500:00:0000:07:4300:02:5806:07:2202:44:4810:18:0620:18:2111:16:3406:46:22
25AUG00:28:0401:10:3800:11:3700:49:1800:00:0000:05:5400:16:1201:24:5300:55:5001:45:1403:11:0307:30:5506:25:28
26SEP00:19:0401:00:0500:02:1100:35:5100:00:0000:03:5300:08:4700:39:3500:06:4700:19:3901:00:1907:42:4507:15:14
27OCT00:07:5000:51:0700:07:0400:43:5200:00:0000:00:0000:00:0000:32:3500:09:3800:08:1700:32:0708:26:2805:39:39
28NOV00:06:4300:33:3900:04:3300:34:1200:00:0000:00:0000:00:0000:06:4500:19:5000:26:3401:21:3607:44:1806:52:32
29DEC01:36:0600:17:2400:00:0000:24:5000:00:0000:00:0000:00:2500:03:4300:02:1300:03:2400:05:1507:10:4905:48:25
30Total40:03:2844:38:4006:55:3721:05:5003:04:5501:42:1201:31:2914:51:0611:01:4422:53:4049:56:10103:31:4995:24:02
OT
Cell Formulas
RangeFormula
B17=B2
B18=B3
C17=C2
C18=C3+0
C30=SUM(C18:C29)
 
Last edited:
Upvote 0
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
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,504
Messages
6,114,016
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top