timesheet formula to give exact time in hours an minutes

[IanNZ]

Hey

This is probably a simple question but i can't figure it out.

new to excel and unsure of what formula i need to use.

I'm trying to create a timesheet that will be able to total hours and minutes to look like hh:mm and then be able to total this for the week.

I know i need to have the date as well as the time but i don't want to have to enter the date with every time worked. I have found a sample timesheet that has the date in a seperate cell and the time in another and then use both to be able to total the time.

e.g

12/4/11
Start 09:50
finish 17:00

total 07:10

this would be for seven days and i'd like to be able to total each day for a full weeks time worked.

thanks to anyone that can help with this. if you need more info on what i need please post and i'll reply.

thanks again, appreciate any help.

 

[wigi]

If you only have 1 cell for the day, you do not have to include it in the subtraction of "finish" - "start".

For instance, if you talk about cells A1 to A3 as above:

=A3-A2

will give the number of hours worked. Format this cell as [h]:mm and you're done for 1 day. Repeat this for the other days, and also 1 sum for the weekly total.

 

[jim may]

Does this help?

Excel Workbook
	A	B	C	D	E	F
1	Date	Start	Stop	Elapse Time
2	12/4/2011	9:50	17:00	7:10		Format: Custom hh:mm
3	12/5/2011	8:30	16:25	7:55
4	12/6/2011	8:15	17:15	9:00		Format: Custom [h]:mm
5	12/7/2011	8:20	15:30	7:10
6	12/8/2011	9:15	14:10	4:55
7				36:10
Sheet1

Excel 2007

Cell Formulas
Range	Formula
D2	=C2-B2
D3	=C3-B3
D4	=C4-B4
D5	=C5-B5
D6	=C6-B6
D7	=SUM(D2:D6)

 

[IanNZ]

hey

thanks so much that adds it all perfect.

when i try to total all the times though for the full week using

=c14+g14+e14+i14+o etc

it only totals two days and ignores the rest?

 

[barry houdini]

Did you format the total cell as Jim suggested? You need to use the custom format [h]:mm so that totals over 24 hours are correctly displayed

 

[IanNZ]

perfect. thanks for the help. seems sooo simple now.