Excel and timesheets

[CiderMonkey]

what do i need to do to be able to get a spreadsheet which works out the hours i have worked each day. If i put the start time in the first column, end time in the second column, what formula should go in the third to work out the hours worked?

the reason i ask is that i need to find out hours worked each day for the past 5 months and this would make it so much easier.

[paddycar]

=b1-a1-x

b1 = time finished a1=time started x= break

that should work.

[Hammer]

First off, format your cells as custom - hh:mm

Then you need to enter your times making sure you use a colon, ie 9:00

Then it's a simple subtraction of one minus the other, ie =B1-A1

[paddycar]

sorry...put your times in as 24hr clock so...

Start = 08:30

Finish = 17:00

lunch = 00:30 <--------------half hour\

lunch = 01:00 <-------------- 1 hourr

[CiderMonkey]

hmm, that's exactly what i was doing but it doesn't appear to be working. I will try again, maybe i am messing up somewhere.

thanks for your help

[Hammer]

Give us an example of one that doesn't work and what answer you get and I'll try and help.

[LeMarchand]

Recently did this, and had a hell of a job getting it to work. The following works, but may not be the most elegant solution (and assumes a start and finish on the same day):

Format your "Start" column (A) as "time", enter the time in the 24hr clock, including a colon to seperate the hours (eg 08:00) - it will automatically add :00 seconds.

Format your "Lunch" column (B) as "general", and enter your break as minutes (eg 30 for a half-hour lunch).

Format your "Finish" column (C) as "time", enter the time in the 24hr clock as column A.

Format your "hours worked" column (D) as a two decimal point number.

The formula for column D, row 1 is =(((C1-A1)*1440)-B1)/60.

The multiplication converts the result of subtracting your start time from your end time into minutes, allowing you to subtract the minutes you took for lunch; and the division converts the result back to hours.

[Adampy]

LeMarchand: Hi I tried your formula as I also have to do timesheets. It works however I alwayts seem to get 7 minutes too many. ie

Start 15:40, Lunch 0, Finish 17:20 = 1.67 (Should it not equal 2 Hrs?)

If I get this it will be a huge help!

[LeMarchand]

Hi - in the example you give you have worked 1 hour 40 minutes, ie 1 and 2/3rd hours or 1.666666666 etc which rounds up to 1.67 hours. If you'd rather use hours:minutes, try this:

Change the format of column D to "Custom" and select hh:mm. Change the formula to =(C1-A1)-(B1/1440). I've just tested it, and that seems to work.

If you have been totalling your hours with a sum [say =SUM(D1:X1)], you will need to change the format of the appropriate cell to Custom/hh:mm as well!

[Adampy]

LeMarchand - You're an absolute star!! You have saved me so much time. You should charge for your services!! One thing, what does the "/1440" do in the formula?

Thanks again! Much appreciated!

[LeMarchand]

1440 is the number of minutes in a day (60*24). I think it's necessary because of the way the cells are formatted in my example - not quite sure why, but it's got something to do with the way Excel performs time calculations.

Like I said, there are probably more elegant ways to do it, but I know my way works!