Automatic Monthly Timesheet

Hi, welcome to the board. It would help if you gave a general layout of your report. Employees in rows? Day 1 thru Day 31 in columns? Looks like you want to enter Start Time and Stop Time--where would these entries go? Don't know what "tardiness should be deducted" means exactly. Are you wanting one formula or several formulas? Might help to give a specific example, then it should be easy for somebody to come up with a formula(s) for you.

Thank you Scott for replying. It is individual time sheet consists of:

Columns for:
Date (rows 1-31), Day, Time-In, Time-Out, Daily Total Hrs, Reg. Hrs (8 hr), Overtime (in excess of 8 hrs), Tardiness (less than 8 hrs work), Sick Leave, Absence, Unpaid Leave. (OT varies if particular date falls on weekend or holiday).

Result should display: Total Hours, Total OT

Hi,

Welcome to MrExcel...

Is this something like you require?
I have had to split the worksheet in 2 so I can post a sample.....




And this.....




You will need to set up some criteria somewhere, either on the same sheet, like my sample above or on a separate "criteria" sheet, you may also want to add a Summary sheet for the year, Absence, Holiday, Overtime etc.

The formulas in B4:H4 require copying down. You will also need formulas to work out the Overtime hours and pay, but I'm sure you will get assistance with that.

Here is a sample (VBA Free) workbook if the data above is like you require, you can then build on it.....

bongards.xls

I hope this helps get you started.
Good luck.

Ak

Thank you very much Akashwani. This would help to get me some idea. Can you teach me how can I include a sample sheet here?

I really appreciate your response, it save me time as well as gain knowledge about it.

Hi,

Take a look here......
http://www.mrexcel.com/forum/showthread.php?t=89356

You cannot attach a Workbook file on here, but you can post a snap shot of your worksheet using the HTML Maker.

Just a note regarding the file I supplied for you, it is NOT 100% as you will need, there are some formulas that need adding and some that will need changing.

You need to think of EVERY possibility when constructing your worksheet and formulas, otherwise, you will find that you will have to add new columns/formulas/formatting etc at a later date. So take your time and give the project plenty of thought.

Here's a new sample of criteria to show what I mean....



The cells in red need formulas to count the number of public holidays.
How will you determine which hours overtime are weekdays, weekends, pubilc holidays? Example change to layout....




The cells highlighted red require formulas for Weekend overtime and public holidays overtime.

I hope these tips give you plenty to think about

Take a look here for some tips on Time....

http://www.youtube.com/user/ExcelIsFun#p/search/8/CtrFnGWqoGE
http://www.youtube.com/user/ExcelIsFun#p/search/11/IKLQpXseH0s
http://www.youtube.com/user/ExcelIsFun#p/search/12/yeXnDslp6xo


Good luck

Ak

Thanks again. I sincerely appreciate your effort and I'll take your advice.

Cheers

Hi,

It's not a problem, I've enjoyed trying to understand all of this myself.

Seeing as it's been quiet here and that you've been so polite and kind with your words, does this solve everything for you?....




And of course your criteria and summary....





And of course the actual file....


bongards.xls


The file has been protected without a password

Good luck with your project.

It's been a pleasure.

Ak

Akashwani,

This timesheet you posted is a perfectly timed masterpiece. I just so happened to be looking for exactly this solution... or almost. So far I have made only one change to your sheet, namely the equation in the E column:
=IF(D2="","",(D2-C2+(D2<C2))-$Q$5)

I did this to compensate for late shifts that might go past midnight.

Also, cell P2 is slightly incorrect if I understand things correctly. Shouldn't it be the sum of column F rather than E?

The rest I am trying to wrap my head around so that I can perform a slightly more complex modification to suit my needs. Where I am located we calculate the overtime rates based on the time of day, rather than the hours in excess of a specific amount of time.
i.e.
8am - 5pm : regular rate
5pm - 8am : regular rate x multiplier1
the whole of the weekend (midnight friday to 8am monday) : regular rate x multiplier2
holidays : regular rate x multiplier3

The typical shift then runs from 8am-4pm, 4pm-midnight, and midnight-8am. This means that the first hour of the shift comes in at a different rate.

Could I appeal to your skills and perhaps get a few tips on how to breakup the hours in a day to apply different rates to each timespan? I really like your method of putting data into criteria, it makes for a much more flexible sheet. In fact, the timespans I list above are likely to change depending on the union specifications for each employee.

Thank you.
M.

Hi M

Yes, the value in P2 is the sum of all regular hours, that should have been changed after I started calculating OT hours, embarrassing eh

I'm not sure if I'm the best person to help you, but thanks for your kind words.
You may want to start a new thread and post a sample of your data with a clear explanation of what you want to do etc. You may need to set up a table of your shift patterns and hourly rates so that you can refer to them in your formulas, eg.....




Take a look here....
http://www.youtube.com/user/ExcelIsFun#p/search/8/CtrFnGWqoGE
http://www.youtube.com/user/ExcelIsFun#p/search/14/IKLQpXseH0s
If this videos don't help, type hours or time into the search box.

I'm sorry that I couldn't have been of more help to you M, I look forward to seeing your new post.

Good luck.

Ak