Automatic Monthly Timesheet

[Bongards]

Dear Fellows,

I just joined this forum and found it so useful with great talents from member about MS Excel.

Hoping somebody can help me out with this problem that I struggled to figure out how to come up with formula to compute the monthly time sheet automatically. It's somehow complicated and I don't know if it's possible.

Calculation is based in daily regular hours work of 8 hours (by default minus 1 hr. for lunch break), so In -8:00AM; Out - 5:30PM should result 8 hrs. and 30 min.

The formula should calculate with the following conditions:

In excess of 8 hours multiplied by 1.5 (Mon-Fri), if less than 8, tardiness must be deducted)
Total hours work multiplied by 1.75 when the day falls on Sat & Sun
Total hours work multiplied by 2.0 when the day falls on Holidays

It should also deduct the total hours of tardiness, absence and unpaid leave.

To generate the monthly time sheet, it should total total hours, total over time, deduction for total tardiness, absence and unpaid leave.

Please, all ideas will be highly appreciate. I don't know how can I post the sample sheet itself to make it clear.


Regards,

 

[Scott R]

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.

 

[Bongards]

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

 

[Akashwani]

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.....

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L
1	Date	Day	Time-In	Time-Out	Daily Total Hrs	Reg. Hrs (8 hr)	Overtime	Tardiness	Sick Leave	Absence	Unpaid Leave	Holiday
2	01/04/2011	Fri	8:00 AM	5:30 PM	08:30	08:00	00:30	*	*	*	*	*
3	02/04/2011	Sat	*	*	*	*	*	*	*	*	*	*
4	03/04/2011	Sun	*	*	*	*	*	*	*	*	*	*
5	04/04/2011	Mon	8:00 AM	6:00 PM	09:00	08:00	01:00	*	*	*	*	*
6	05/04/2011	Tue	8:00 AM	5:00 PM	08:00	08:00	00:00	*	*	*	*	*
7	06/04/2011	Wed	8:00 AM	4:00 PM	07:00	*	*	Left early	*	*	*	*
8	07/04/2011	Thu	8:00 AM	7:00 PM	10:00	08:00	02:00	*	*	*	*	*
9	08/04/2011	Fri	8:30 AM	5:00 PM	07:30	*	*	Started Late	*	*	*	*
10	09/04/2011	Sat	8:00 AM	5:00 PM	08:00	00:00	08:00	*	*	*	*	*
11	10/04/2011	Sun	*	*	*	*	*	*	*	*	*	*
12	11/04/2011	Mon	8:15 AM	5:00 PM	07:45	*	*	Started Late	*	*	*	*
13	12/04/2011	Tue	8:00 AM	4:00 PM	07:00	*	*	Left early	*	*	*	*
14	13/04/2011	Wed	*	*	*	*	*	*	*	*	*	H
15	14/04/2011	Thu	*	*	*	*	*	*	*	A	*	*
16	15/04/2011	Fri	*	*	*	*	*	*	S	*	*	*
Sheet1

And this.....

Excel Workbook
	N	O	P	Q	R	S
1	Total Hours	Total OT Hrs	Standard Pay	OT Pay	Total Pay	*
2	72:45:00	*	582.00	*	*	*
3	*	*	*	*	*	*
4	Working Day	Lunch	Hourly Rate	OT Week	OT Sat/Sun	OT Holidays
5	08:00	01:00	$8.00	$12.00	$14.00	$16.00
6	Start Time	*	*	*	*	*
7	8:00 AM	*	*	*	*	*
8	End Time	*	*	*	*	*
9	5:00 PM	*	*	*	*	*
10	*	*	*	*	*	*
11	Sick Leave	1	*	*	*	*
12	Absence	1	*	*	*	*
13	Unpaid Leave	0	*	*	*	*
14	Holiday	1	*	*	*	*
Sheet1

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

 

[Bongards]

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.

 

[Akashwani]

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....

Excel Workbook
	P	Q	R	S	T	U
1	Total Reg Hrs	Total OT Hrs	Standard Pay	OT Pay	Monthly Pay	Monthly Hours
2	72:45:00	3:30	$582.00	$42.00	$624.00	76:15:00
3	*	*	*	*	*	*
4	Working Day	Lunch	Hourly Rate	OT Week	OT Sat/Sun	OT Holidays
5	8	1	$8.00	$12.00	$14.00	$16.00
6	Start Time	*	*	*	*	*
7	8:00 AM	*	*	*	*	*
8	End Time	*	*	*	*	*
9	5:00 PM	*	*	*	*	*
10	*	*	*	*	*	*
11	Sick Leave	1	*	*	*	Holiday Dates
12	Absence	1	*	*	*	17/01/2011
13	Unpaid Leave	0	*	*	*	21/02/2011
14	Holiday	1	*	*	*	30/05/2011
15	*	*	*	*	*	04/07/2011
16	Overtime	Hours	Pay	*	*	05/09/2011
17	Week	3:30	$42.00	*	*	10/10/2011
18	Sat	0:00	$0.00	*	*	11/11/2011
19	Sun	0:00	$0.00	*	*	24/11/2011
20	Holidays	*	$0.00	*	*	26/12/2011
21	*	*	*	*	*	*
22	This Month	*	*	*	*	*
23	Weekdays	21	*	*	*	*
24	Holiday Dates	*	*	*	*	*
April

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....

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	Date	Day	Time-In	Time-Out	Daily Total Hrs	Reg. Hrs (8 hr)	Week OT	W/E OT	Holiday OT
2	01/04/2011	Fri	8:00 AM	5:30 PM	08:30	08:00	00:30	*	*
3	02/04/2011	Sat	8:00 AM	5:30 PM	08:30	*	*	08:30	*
April

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

 

[Bongards]

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

Cheers

 

[Akashwani]

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?....

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Date	Day	Time-In	Time-Out	Daily Total Hrs	Reg. Hrs (8 hr)	Week OT	W/E OT	Holiday OT	Tardiness	Sick Leave	Absence	Unpaid Leave	Holiday
2	01/04/2011	Fri	8:00 AM	5:30 PM	08:30	08:00	00:30	*	*	*	*	*	*	*
3	02/04/2011	Sat	8:00 AM	5:30 PM	08:30	*	*	8:30	*	*	*	*	*	*
4	03/04/2011	Sun	*	*	*	*	*	*	*	*	*	*	*	*
5	04/04/2011	Mon	8:00 AM	6:00 PM	09:00	08:00	01:00	*	*	*	*	*	*	*
6	05/04/2011	Tue	8:00 AM	5:00 PM	08:00	08:00	*	*	*	*	*	*	*	*
7	06/04/2011	Wed	8:00 AM	4:00 PM	07:00	07:00	*	*	*	Left early	*	*	*	*
8	07/04/2011	Thu	8:00 AM	7:00 PM	10:00	08:00	02:00	*	*	*	*	*	*	*
9	08/04/2011	Fri	8:30 AM	5:00 PM	07:30	07:30	*	*	*	Started Late	*	*	*	*
10	09/04/2011	Sat	8:00 AM	5:00 PM	08:00	*	*	8:00	*	*	*	*	*	*
11	10/04/2011	Sun	*	*	*	*	*	*	*	*	*	*	*	*
12	11/04/2011	Mon	8:15 AM	5:00 PM	07:45	07:45	*	*	*	Started Late	*	*	*	*
13	12/04/2011	Tue	8:00 AM	4:00 PM	07:00	07:00	*	*	*	Left early	*	*	*	*
14	13/04/2011	Wed	*	*	*	*	*	*	*	*	*	*	*	H
15	14/04/2011	Thu	*	*	*	*	*	*	*	*	*	A	*	*
16	15/04/2011	Fri	*	*	*	*	*	*	*	*	S	*	*	*
April

And of course your criteria and summary....

Excel Workbook
	P	Q	R	S	T	U
1	Total Reg Hrs	Total OT Hrs	Standard Pay	OT Pay	Monthly Pay	Monthly Hours
2	81:15:00	20:00	$650.00	$273.00	$923.00	101:15:00
3	*	*	*	*	*	*
4	Working Day	Lunch	Hourly Rate	OT Week	OT Sat/Sun	OT Holidays
5	8	1	$8.00	$12.00	$14.00	$16.00
6	Start Time	*	*	*	*	*
7	8:00 AM	*	*	*	*	*
8	End Time	*	*	*	*	*
9	5:00 PM	*	*	*	*	*
10	*	*	*	*	*	*
11	Sick Leave	1	*	*	*	HolidayDates
12	Absence	1	*	*	*	17/01/2011
13	Unpaid Leave	0	*	*	*	21/02/2011
14	Holiday	1	*	*	*	30/05/2011
15	*	*	*	*	*	04/07/2011
16	Overtime	Hours	Pay	*	*	05/09/2011
17	Week	3:30	$42.00	*	*	10/10/2011
18	Sat	16:30	$231.00	*	*	11/11/2011
19	Sun	0:00	$0.00	*	*	24/11/2011
20	Holidays	0:00	$0.00	*	*	26/12/2011
21	*	*	*	*	*	*
22	This Month	*	*	*	*	*
23	Weekdays	21	*	*	*	*
24	Weekend Days	9	*	*	*	*
25	Holiday Dates	0	*	*	*	*
April

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

 

[type88]

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.

 

[Akashwani]

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.....

Excel Workbook
	E	F	G	H	I
1		Start Time	End Time	No Hours	Hourly rates
2	1	8:00 AM	5:00 PM	9:00	$8.00
3	2	5:00 PM	8:00 AM	15:00
4	3	12:00 AM	8:00 AM	8:00	$16.00
5	4	Holidays			$24.00
Criteria

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