Daily/Weekly Time Sheets

[accountant1659]

I am trying to create a generic time sheet for employees that work part time, full time, and over time. Employees do not clock out for lunch, so 30 minutes is deducted from the daily total.

My equation for daily is =(((B9-B8)+(B11-B10)+(B13-B12)+(B15-B14)+(B17-B16)+(B19-B18))*(24-0.5)) which gives me the correct total of hours worked for the day.

My equation for weekly is =(((B9-B8)+(C9-C8)+(D9-D8)+(E9-E8)+(F9-F8)+(G9-G8)+(H9-H8))*(24)) which does not always give me the correct total of hours.

[TABLE="width: 728"]
<colgroup><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Contract/Job Description[/TD]
[TD]Wed[/TD]
[TD]Thurs[/TD]
[TD]Fri[/TD]
[TD]Sat[/TD]
[TD]Sun[/TD]
[TD]Mon[/TD]
[TD]Tues[/TD]
[TD]Total Hours[/TD]
[/TR]
[TR]
[TD] In[/TD]
[TD="align: right"]7:00 AM[/TD]
[TD="align: right"]7:00 AM[/TD]
[TD="align: right"]7:00 AM[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]7:00 AM[/TD]
[TD="align: right"]7:00 AM[/TD]
[TD] 18[/TD]
[/TR]
[TR]
[TD] Out[/TD]
[TD="align: right"]8:00 AM[/TD]
[TD="align: right"]9:00 AM[/TD]
[TD="align: right"]5:30 PM[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]9:00 AM[/TD]
[TD="align: right"]9:00 AM[/TD]
[/TR]
[TR]
[TD] In[/TD]
[TD="align: right"]8:00 AM[/TD]
[TD="align: right"]9:00 AM[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]9:00 AM[/TD]
[TD="align: right"]9:00 AM[/TD]
[TD] 7[/TD]
[/TR]
[TR]
[TD] OUt[/TD]
[TD="align: right"]9:00 AM[/TD]
[TD="align: right"]11:00 AM[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]11:00 AM[/TD]
[TD="align: right"]11:00 AM[/TD]
[/TR]
[TR]
[TD] In[/TD]
[TD="align: right"]9:00 AM[/TD]
[TD="align: right"]11:00 AM[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]11:00 AM[/TD]
[TD] [/TD]
[TD] 7[/TD]
[/TR]
[TR]
[TD] Out[/TD]
[TD="align: right"]10:00 AM[/TD]
[TD="align: right"]2:00 PM[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]2:00 PM[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] In[/TD]
[TD="align: right"]10:00 AM[/TD]
[TD="align: right"]2:00 PM[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]2:00 PM[/TD]
[TD] [/TD]
[TD] 4[/TD]
[/TR]
[TR]
[TD] Out[/TD]
[TD="align: right"]11:00 AM[/TD]
[TD="align: right"]3:00 PM[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]3:30 PM[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] In[/TD]
[TD="align: right"]11:00 AM[/TD]
[TD="align: right"]3:00 PM[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] 4[/TD]
[/TR]
[TR]
[TD] Out[/TD]
[TD="align: right"]2:00 PM[/TD]
[TD="align: right"]4:00 PM[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] In[/TD]
[TD="align: right"]2:00 PM[/TD]
[TD="align: right"]4:00 PM[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] 3[/TD]
[/TR]
[TR]
[TD] Out[/TD]
[TD="align: right"]3:30 PM[/TD]
[TD="align: right"]5:30 PM[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Total Hours:[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]4[/TD]
[TD]Reg Hours[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]42[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]OT Hours[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[/TR]
</tbody>[/TABLE]

How can I get this excel spreed sheet formulated correctly for all employees to use the same form?

Thanks for your help,
Accountant1659

 

[QU4487]

Have a look at the attached xlsx. Hopefully it'll do what you're after.

https://www.dropbox.com/s/9msfg82gesvl6k4/Daily-Weekly Timesheet.xlsx

Hope it helps.

Ps. If the formulas look a bit excessive, it's due to the fact that I've taken into account days when no work is logged. I'm hope it'll make sense.

 

[accountant1659]

Have a look at the attached xlsx. Hopefully it'll do what you're after.

https://www.dropbox.com/s/9msfg82gesvl6k4/Daily-Weekly Timesheet.xlsx

Hope it helps.

Ps. If the formulas look a bit excessive, it's due to the fact that I've taken into account days when no work is logged. I'm hope it'll make sense.

Thanks, I'll take a look at it.