Adding Time

G

Guest

Column A Column B Column C
4/3/07 5:12 PM 4/3/07 7:00 PM 1:48
4/3/07 3:50 PM 4/4/07 7:00 PM 3:10
4/3/07 6:36 PM 4/4/07 4:10 PM 21:34

Column C formated as h:mm

Need help with formula to sum time.

Reg [sum] I get 2:32 then *24 I get 12:48, the correct answer should be 26:32.

Thanks!
 
J

JE McGimpsey

Format/Cells/Number/Custom [h]:mm

The brackets keep the display engine from rolling over time at 24 hours.
 
G

Guest

To sum column C just use the regular function =sum(C1:C3). The cell with the
sum function change the format to a number with 2 or more decimal places.
The sum will be in days. To get hours simply multiply days by 24. To get
minutes multiply by 24 * 60.
 
G

Guest

I tried that, but still not getting the desired answer.
--
Texas Wannano


JE McGimpsey said:
Format/Cells/Number/Custom [h]:mm

The brackets keep the display engine from rolling over time at 24 hours.

Wannano said:
Column A Column B Column C
4/3/07 5:12 PM 4/3/07 7:00 PM 1:48
4/3/07 3:50 PM 4/4/07 7:00 PM 3:10
4/3/07 6:36 PM 4/4/07 4:10 PM 21:34

Column C formated as h:mm

Need help with formula to sum time.

Reg [sum] I get 2:32 then *24 I get 12:48, the correct answer should be 26:32.

Thanks!
Click to expand...
Click to expand...
 
G

Guest

That didn't worked either...I got 50:52, which is still incorrect. What
could I be doing wrong or not doing?

Thanks!
--
Texas Wannano


Joel said:
To sum column C just use the regular function =sum(C1:C3). The cell with the
sum function change the format to a number with 2 or more decimal places.
The sum will be in days. To get hours simply multiply days by 24. To get
minutes multiply by 24 * 60.

Wannano said:
Column A Column B Column C
4/3/07 5:12 PM 4/3/07 7:00 PM 1:48
4/3/07 3:50 PM 4/4/07 7:00 PM 3:10
4/3/07 6:36 PM 4/4/07 4:10 PM 21:34

Column C formated as h:mm

Need help with formula to sum time.

Reg [sum] I get 2:32 then *24 I get 12:48, the correct answer should be 26:32.

Thanks!
Click to expand...
Click to expand...
 
D

Dave Peterson

Try formatting C2 (all of column C) as: [hh]:mm:ss

3:10 is really 1 day 3 hours and 10 minutes (27:10:00).


Column A Column B Column C
4/3/07 5:12 PM 4/3/07 7:00 PM 1:48
4/3/07 3:50 PM 4/4/07 7:00 PM 3:10
4/3/07 6:36 PM 4/4/07 4:10 PM 21:34

Column C formated as h:mm

Need help with formula to sum time.

Reg [sum] I get 2:32 then *24 I get 12:48, the correct answer should be 26:32.

Thanks!
Click to expand...
 
G

Guest

I put your data into my worksheet and got 2.3 days. The real answer you
have to subtract one from the sum. the correct answer is 1.3 days.

4/3/2007 17:12 4/3/2007 19:00 1:48
4/3/2007 15:50 4/4/2007 19:00 3:10
4/3/2007 18:36 4/4/2007 16:10 21:34

2.11 days subtract 1 and get 1.11 days



Wannano said:
That didn't worked either...I got 50:52, which is still incorrect. What
could I be doing wrong or not doing?

Thanks!
--
Texas Wannano


Joel said:
To sum column C just use the regular function =sum(C1:C3). The cell with the
sum function change the format to a number with 2 or more decimal places.
The sum will be in days. To get hours simply multiply days by 24. To get
minutes multiply by 24 * 60.

Wannano said:
Column A Column B Column C
4/3/07 5:12 PM 4/3/07 7:00 PM 1:48
4/3/07 3:50 PM 4/4/07 7:00 PM 3:10
4/3/07 6:36 PM 4/4/07 4:10 PM 21:34

Column C formated as h:mm

Need help with formula to sum time.

Reg [sum] I get 2:32 then *24 I get 12:48, the correct answer should be 26:32.

Thanks!
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

I was wrong the answer you got is correct. Look at results below. I add
column D which is the same as column C except in number format. The second
row isn't 3:10 it is actually 27:10. The format you are using in column C is
masking the fact it is actually more than one day.


4/3/2007 17:12 4/3/2007 19:00 1:48 0.07
4/3/2007 15:50 4/4/2007 19:00 3:10 1.13
4/3/2007 18:36 4/4/2007 16:10 21:34 0.90

2.11


Wannano said:
That didn't worked either...I got 50:52, which is still incorrect. What
could I be doing wrong or not doing?

Thanks!
--
Texas Wannano


Joel said:
To sum column C just use the regular function =sum(C1:C3). The cell with the
sum function change the format to a number with 2 or more decimal places.
The sum will be in days. To get hours simply multiply days by 24. To get
minutes multiply by 24 * 60.

Wannano said:
Column A Column B Column C
4/3/07 5:12 PM 4/3/07 7:00 PM 1:48
4/3/07 3:50 PM 4/4/07 7:00 PM 3:10
4/3/07 6:36 PM 4/4/07 4:10 PM 21:34

Column C formated as h:mm

Need help with formula to sum time.

Reg [sum] I get 2:32 then *24 I get 12:48, the correct answer should be 26:32.

Thanks!
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

That worked Dave.

Thanks a mil!
--
Texas Wannano


Dave Peterson said:
Try formatting C2 (all of column C) as: [hh]:mm:ss

3:10 is really 1 day 3 hours and 10 minutes (27:10:00).


Column A Column B Column C
4/3/07 5:12 PM 4/3/07 7:00 PM 1:48
4/3/07 3:50 PM 4/4/07 7:00 PM 3:10
4/3/07 6:36 PM 4/4/07 4:10 PM 21:34

Column C formated as h:mm

Need help with formula to sum time.

Reg [sum] I get 2:32 then *24 I get 12:48, the correct answer should be 26:32.

Thanks!
Click to expand...
Click to expand...
 
G

Guest

It worked.

Thanks a lot!
--
Texas Wannano


Joel said:
I was wrong the answer you got is correct. Look at results below. I add
column D which is the same as column C except in number format. The second
row isn't 3:10 it is actually 27:10. The format you are using in column C is
masking the fact it is actually more than one day.


4/3/2007 17:12 4/3/2007 19:00 1:48 0.07
4/3/2007 15:50 4/4/2007 19:00 3:10 1.13
4/3/2007 18:36 4/4/2007 16:10 21:34 0.90

2.11


Wannano said:
That didn't worked either...I got 50:52, which is still incorrect. What
could I be doing wrong or not doing?

Thanks!
--
Texas Wannano


Joel said:
To sum column C just use the regular function =sum(C1:C3). The cell with the
sum function change the format to a number with 2 or more decimal places.
The sum will be in days. To get hours simply multiply days by 24. To get
minutes multiply by 24 * 60.

:

Column A Column B Column C
4/3/07 5:12 PM 4/3/07 7:00 PM 1:48
4/3/07 3:50 PM 4/4/07 7:00 PM 3:10
4/3/07 6:36 PM 4/4/07 4:10 PM 21:34

Column C formated as h:mm

Need help with formula to sum time.

Reg [sum] I get 2:32 then *24 I get 12:48, the correct answer should be 26:32.

Thanks!
Click to expand...
Click to expand...
Click to expand...
 
J

JE McGimpsey

I'm confused. You say the correct answer should be 26:32, but you also
mention multiplying by 24, which should give you a decimal time.

IF you're summing and multiplying by 24, just reformat the cell as
General to get decimal time.

If you're trying to get an XL time value, don't multiply by 24.

And for future reference, saying "still not getting the desired answer"
provides almost zero information - what answer ARE you getting?


Wannano said:
I tried that, but still not getting the desired answer.
--
Texas Wannano


JE McGimpsey said:
Format/Cells/Number/Custom [h]:mm

The brackets keep the display engine from rolling over time at 24 hours.

Wannano said:
Column A Column B Column C
4/3/07 5:12 PM 4/3/07 7:00 PM 1:48
4/3/07 3:50 PM 4/4/07 7:00 PM 3:10
4/3/07 6:36 PM 4/4/07 4:10 PM 21:34

Column C formated as h:mm

Need help with formula to sum time.

Reg [sum] I get 2:32 then *24 I get 12:48, the correct answer should be
26:32.

Thanks!
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Average Time for Specific Item 9
Grouping Time in Pivots. 2
Subtracting time...again 2
Subtracting Dates - Mike 4
Grouping. 8
Looking for formula 20
Formatting time 2
WCG Stats Thursday 10 August 2023 3

Top