Calculate Total Time

[Robby19]

Good day!

I have a table that looks like
[TABLE="width: 500"]
<TBODY>[TR]
[TD]Day
[/TD]
[TD]IN
[/TD]
[TD]OUT
[/TD]
[TD]IN
[/TD]
[TD]OUT
[/TD]
[TD]Hours
[/TD]
[/TR]
[TR]
[TD]1-Jul
[/TD]
[TD]8:00
[/TD]
[TD]12:00
[/TD]
[TD]13:00
[/TD]
[TD]17:00
[/TD]
[TD]8 hours, 0 minutes
[/TD]
[/TR]
[TR]
[TD]2-Jul
[/TD]
[TD]8:00
[/TD]
[TD]12:00
[/TD]
[TD]13:00
[/TD]
[TD]17:30
[/TD]
[TD]8 hours, 30 minutes
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Total Hours
[/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]

Hours column is formatted as such, h "hours," m "minutes" and is a calculated column;

=(e10-d10)+(g10-f10)

I need to calculate the Total Hours. In this example, it should say 16 hours, 30 minutes. I tried using a SUM, but it only seems to show the maximum for some reason. Can anyone help me?

 

[Robby19]

Figured it out. It should be

=SUM(H10:H11)*24

and formatted as a Number!

 

[JoeMo]

Here's an alternative way. F2:F4 are custom format: [h]" hours, "m" minutes"

Excel Workbook
	A	B	C	D	E	F
1	Day	IN	OUT	IN	OUT	Hours
2	1-Jul	8:00	12:00	13:00	17:00	8 hours, 0 minutes
3	2-Jul	8:00	12:00	13:00	17:30	8 hours, 30 minutes
4					Total Hours	16 hours, 30 minutes
Sheet2

 

[Robby19]

Thank you for the response!

Is there a way to format the cell so that if I type in 800, it will automatically adjust to 8:00?

 

[JoeMo]

Thank you for the response!

Is there a way to format the cell so that if I type in 800, it will automatically adjust to 8:00?

You are welcome.

Here's one way that uses a helper column. Let' say that's col A for this example. First, format the helper column as text. Then in A1 enter 0800 (4 digits). Format col B as "h:mm". In B1 enter: =(LEFT(A1,2)&":"&RIGHT(A1,2))+0
B1 will now hold a time (a number not text) of : 8:00.

 

[Zenwood]

Also remember that Shift + CTRL + : will put the current time in a cell.