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?

Column A​	Column B​	Column C​	Column D​
Part​	Type​	Time​	Inspection Time​
Part A​	​	Total​	0.05555556​
Part A​	​	13:30​	​
Part A​	​	13:45​	0:15​
Part A​	​	13:45​	0:00​
Part A​	Set-up​	14:50​	0:00​
Part A​	​	15:05​	0:15​
Part A​	​	15:40​	0:35​
Part A​	​	15:55​	0:15​
Part A​	Set-up​	16:15​	0:00​
Part A​	​	16:15​	0:00​

<tbody> </tbody>

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