Adding up time from timestamps in excel

[DerekWooley]

Hello, I am trying to add up the total time differencebetween certain time stamps and when I do, I’m not sure how to convert thenumbers into hours for to get the correct sum. I have tried custom cellformatting but that doesn’t change the way excel sees inside the cell formulas.Below is the table I am using: it starts on Row 1, Column A. I am subtractingthe previous time from each time stamp except where it says “set-up” in columnB so my formula in Column D is on Row 4 is =IF(B5="",C5-C4,0). When Iadd all that up it should be 1.20 hours but when I sum it in excel, it shows.056. How do I get excel to calculate 1.20 hours?

[TABLE="width: 255"]
<tbody>[TR]
[TD="width: 81, bgcolor: transparent"]

Column A​

[/TD]
[TD="width: 81, bgcolor: transparent"]

Column B​

[/TD]
[TD="width: 81, bgcolor: transparent"]

Column C​

[/TD]
[TD="width: 96, bgcolor: transparent"]

Column D​

[/TD]
[/TR]
[TR]
[TD="width: 81"]

Part​

[/TD]
[TD="width: 81"]

Type​

[/TD]
[TD="width: 81"]

Time​

[/TD]
[TD="width: 96"]

Inspection Time​

[/TD]
[/TR]
[TR]
[TD="width: 81, bgcolor: transparent"]

Part A​

[/TD]
[TD="width: 81"]

​

[/TD]
[TD="width: 81"]

Total​

[/TD]
[TD="width: 96"]

0.05555556​

[/TD]
[/TR]
[TR]
[TD="width: 81, bgcolor: transparent"]

Part A​

[/TD]
[TD="width: 81, bgcolor: transparent"]

​

[/TD]
[TD="width: 81, bgcolor: transparent"]

13:30​

[/TD]
[TD="width: 96, bgcolor: transparent"]

​

[/TD]
[/TR]
[TR]
[TD="width: 81, bgcolor: transparent"]

Part A​

[/TD]
[TD="width: 81, bgcolor: transparent"]

​

[/TD]
[TD="width: 81, bgcolor: transparent"]

13:45​

[/TD]
[TD="width: 96, bgcolor: transparent"]

0:15​

[/TD]
[/TR]
[TR]
[TD="width: 81, bgcolor: transparent"]

Part A​

[/TD]
[TD="width: 81, bgcolor: transparent"]

​

[/TD]
[TD="width: 81, bgcolor: transparent"]

13:45​

[/TD]
[TD="width: 96, bgcolor: transparent"]

0:00​

[/TD]
[/TR]
[TR]
[TD="width: 81, bgcolor: transparent"]

Part A​

[/TD]
[TD="width: 81, bgcolor: transparent"]

Set-up​

[/TD]
[TD="width: 81, bgcolor: transparent"]

14:50​

[/TD]
[TD="width: 96, bgcolor: transparent"]

0:00​

[/TD]
[/TR]
[TR]
[TD="width: 81, bgcolor: transparent"]

Part A​

[/TD]
[TD="width: 81, bgcolor: transparent"]

​

[/TD]
[TD="width: 81, bgcolor: transparent"]

15:05​

[/TD]
[TD="width: 96, bgcolor: transparent"]

0:15​

[/TD]
[/TR]
[TR]
[TD="width: 81, bgcolor: transparent"]

Part A​

[/TD]
[TD="width: 81, bgcolor: transparent"]

​

[/TD]
[TD="width: 81, bgcolor: transparent"]

15:40​

[/TD]
[TD="width: 96, bgcolor: transparent"]

0:35​

[/TD]
[/TR]
[TR]
[TD="width: 81, bgcolor: transparent"]

Part A​

[/TD]
[TD="width: 81, bgcolor: transparent"]

​

[/TD]
[TD="width: 81, bgcolor: transparent"]

15:55​

[/TD]
[TD="width: 96, bgcolor: transparent"]

0:15​

[/TD]
[/TR]
[TR]
[TD="width: 81, bgcolor: transparent"]

Part A​

[/TD]
[TD="width: 81, bgcolor: transparent"]

Set-up​

[/TD]
[TD="width: 81, bgcolor: transparent"]

16:15​

[/TD]
[TD="width: 96, bgcolor: transparent"]

0:00​

[/TD]
[/TR]
[TR]
[TD="width: 81, bgcolor: transparent"]

Part A​

[/TD]
[TD="width: 81, bgcolor: transparent"]

​

[/TD]
[TD="width: 81, bgcolor: transparent"]

16:15​

[/TD]
[TD="width: 96, bgcolor: transparent"]

0:00​

[/TD]
[/TR]
</tbody>[/TABLE]

Thank you for any help!

 

[jtakw]

Hi,

You just need to format your result cell (D1) as Time to show the sum the way you want.

 

[DerekWooley]

Ok so say I have 4 hours that I need to add the total from my table to. How do I convert the time differences from the time stamps to numbers so when I add the total of the differences it adds up to 5.20 (4 plus 1.20). Converting D1 to custom format h:mm only changed the way it appeared but not how it was added to the 4 hour general number; I still got 4.056 instead of 5.2.

FYI, I am pulling these time stamp differences into another table that has number of setup hours as numbers which can't be time format so I don't want to convert to time format. Maybe there is a formula that can be applied to the time stamp differences to make them more like numbers?

 

[Zenwood]

Tray adding 4:00 to 1:20

 

[DerekWooley]

I tried that but then on my other table I have a sumproduct formula that comes from these things that are machine counters that are not in time format but still indicate time differences add up to like 88 hours each instance. 4:00 ends up being .2 as a number.

I think what I am looking for is a formula like Value() but to work for time. For example, Change 0:15 to just 15, 0:35 to 35.

 

[jtakw]

If you want to Add 1 hour to an Actual Time Value of 1:20 (1 hour 20 minutes), you'll need to convert the 1 to Time Value, Not the other way around.
In Excel, 1 is 1 day, 2 is 2 days, 3 is 3 days, etc...

See below:

Book1
	A	B	C	D	E	F
1	Part	Type	Time	Inspection Time		Add 4 hours to D2
2	Part A		Total	1:20	<-- format cell as Time or hh:mm	5:20
3	Part A		13:30
4	Part A		13:45	0:15
5	Part A		13:45	0:00
6	Part A	Set-up	14:50	0:00
7	Part A		15:05	0:15
8	Part A		15:40	0:35
9	Part A		15:55	0:15
10	Part A	Set-up	16:15	0:00
11	Part A		16:15	0:00
Sheet306

Cell Formulas
Range	Formula
D2	=SUM(D3:D11)
F2	=4/24+D2