Building Custom Timesheets

[slaceysr]

I am familiar with the timesheet template in Excel that takes user input for a time in and a time out and then does the same thing again, as if clocking out and back in for lunch or break. I am in need of a custom timesheet that only takes 1 clock-in and 1 clock-out time then calculates the hours worked.


What I came up with will take the in and out times and return hours worked:

In: 8:00

Out: 16:30

Hours worked: 8:30


My first problem is that the hours worked should read 8:00 but, try as I might, I can't get Excel to subtract the 30 minutes for lunch.

My second problem is that when I add the total for the week I get 18:30 instead of 42:30 (which is the 8:30 hours times 5 days). I should get 40:00 hours as my total.


The cells are set for the time data type. To calculate 'hours worked' I used 'B1-A1', where B1 is 'time out' and A1 is 'time in'. For weekly total I just added the cells for 'hours worked'. I thought this would be sufficient but is not giving the desired output.

I imagine the solution is something simple that I have just not stumbled upon yet. If I have to I will use the Excel template but would rather learn how to accomplish this.

Thanks in advance,

Sid

 

[Luke M]

To get the correct hours worked, using a standard 30 minute lunch, formula becomes:

=B1-A1-(1/48)

Where the latter represents the numerical equivalent of 30 minutes. (Derivation: Take 1 day / 24 hrs / 60 min * 30 min)


Your 2nd problem is simply a format issue. Change custom number format to:

[h]:mm

 

[slaceysr]

Thanks so much.