Help With Timesheet

[Dazzawm]

I am creating a timesheet and need help with the formula to make the total worked for that day. I want to be able to enter the times as I have them if possible rather than something like 08:00:00 17:00:00.

<TABLE style="WIDTH: 144pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=192><COLGROUP><COL style="WIDTH: 48pt" span=3 width=64><TBODY><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=21 width=64>A</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64>B</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64>C</TD></TR><TR style="HEIGHT: 19.5pt" height=26><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 19.5pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=26>Start</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>Finish</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>Total</TD></TR><TR style="HEIGHT: 18.75pt" height=25><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 18.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=25>09.00</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>17.00</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD></TR></TBODY></TABLE>

I will be able to do all the others and do the total at the bottom, my minds gone blank for now and just need a start. Please bear in mind that nights may be worked so they may sort of overlap and it may think that the finish time is before the start time!

 

[VoG]

It is much easier to enter Excel times if you have shifts spanning midnight. Note that C6 is formatted as Custom [h]:mm

Excel Workbook
	A	B	C
1	17:00	04:30	11:30
2	06:00	16:30	10:30
3	09:00	17:00	08:00
4	19:30	03:00	07:30
5	07:30	15:30	08:00
6			45:30
Sheet7

 

[Dazzawm]

Thanks VoG could you just explain the formula in C1 to aid my learning

 

[VoG]

Hopefully ...

MOD(a-b,1) returns a positive number which is the remainder of subtracting b from a and dividing by 1. That will thus always return a positive time difference.

 

[Dazzawm]

Thanks VoG, all done. (What does the 'MOD' mean)?

 

[VoG]

MOD is modulus (or modulo). See http://en.wikipedia.org/wiki/Modulo_operation

 

[Dazzawm]

Oh another thing, this is for a mate that is useless with excel! I believe I can protect the cells with formulas in so he can't delete them accidently? I just want him to put in the start and finish time and let the formulas do the rest.

 

[VoG]

Select the cells where he can enter times, press CTRL + 1, on the Protection tab untick Locked. Then protect the sheet.

 

[Dazzawm]

If I select CTRL + 1 an 'insert' box comes up

 

[VoG]

CTRL + 1 should bring up the Format cells dialog.

In Excel 2007, on the Home tab click Format then select Format Cells (the bottom choice).