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,

 

[type88]

Akashwani,

I am actually surprising myself with my ability to understand what you have created here. I have all of the various overtime hours calculating correctly now, and have just discovered that where I am located we have two types of holidays, regular ones and 'get rich if you have to work on one' holidays.

Adding this column might be the only thing that gives me grief as I don't quite understand the use of NETWORKDAYS.

M

 

[Akashwani]

Hey M,

Networkdays is easier to understand than you think.

=Networkdays(Start Date,End Date,Holidays)

How many weekdays between Start Date and End Date minus any Holidays...

Excel Workbook
	C	D	E	F	G	H
1	Start Date	End Date	Days	Working Days	*	Holidays
2	21/04/2011	04/05/2011	13	6	*	22/04/2011
3	*	*	*	*	*	25/04/2011
4	*	*	*	*	*	29/04/2011
5	*	*	*	*	*	02/05/2011
Sheet1

I always Name the Holiday range, in this case it's Holidays.

I hope that helps M.

Good luck.

Ak

 

[Bongards]

Again, thanks Ak for your brilliant ideas. Can't thank you enough for the quick replies coupled with solutions. Now is my turn to understand how you did it, the formulas and functions to improve my knowledge.

Keep it up.

 

[Akashwani]

Hi,

You may be interested in this....

http://www.mrexcel.com/forum/showthread.php?p=2702786#post2702786

Ak

 

[type88]

Well I had everything done just the way I needed it, and even though I save the file it became corrupt when my laptop battery died. I thought to share my results but that will have to wait until I get this done all over again.

M.

 

[Akashwani]

Hi M, sorry I have to laugh, I have lost count of the amount of times I have forgot to save data, then my laptop goes and acts like its drunk or something.

Have you set Excel to Save AutoRecover information every XX minutes?

ALT F I click save, check the box and set your number of minutes to suit, click OK.

I hope that you get to recover your file as I am interested in seeing your end result.

Ak

 

[type88]

As it turns out I was saving the work with each and every step, so it was a surprise when it went all corrupt on me.

I have redone my changes though, and for the benefit of all the changes are here:

To the right of the Holidays column I added the High Holidays column. I also created three named ranges: AllHolidayDates, HolidayDates, and HighHolidayDates. I am no good with arrays, and since named ranges must be contiguous, they are all in a row for the sake of creating the AllHolidayDates.

The Start and End Times I also left, as they are nice to have changeable.

Here are the column headers and formulas, the right hand side of the sheet was modified to include the extra column and such:

Daily Total Hrs
=IF(D2="","",D2-C2-$Q$5)

Daytime Hours
=IF(NETWORKDAYS($A2,$A2,AllHolidayDates),IF(D2="","",IF(D2>$P$9,(($P$9-C2+($P$9<C2))),E2)),"")

Evening
=IF(OR($E2=""),"",IF(NETWORKDAYS($A2,$A2,AllHolidayDates),(D2-$P$9+(D2<$P$9)),""))

Weekend
=IF(AND(G2="",I2="",F2="", J2=""),E2,"")

Holiday OT
=IF(ISNA(IF(MATCH(A2,HolidayDates,0)>0,E2,"")),"",IF(MATCH(A2,HolidayDates,0)>0,E2,""))

High Holiday
=IF(ISNA(IF(MATCH(A2,HighHolidayDates,0)>0,E2,"")),"",IF(MATCH(A2,HighHolidayDates,0)>0,E2,""))

I have basically modified all of the original formulas to add one more check of whether the TimeOut was after the End Time.

I hope this helps anyone else in need.
M.

 

[Akashwani]

Hi M,

Check this file out if you want to be able to create a sheet for every month of the year....

MonthlyTimeSheet2.xls

Ak

 

[type88]

2 things:
1. My Excel just crashed again, spoiling my efforts once more. I have never had a problem with excel corrupting a saved file when it crashes. Time to make multiple copies and investigate.
2. Time to look into macros, this new sheet of yours is a timesaver.

 

[Bongards]

Hi AK,

I like the way you did in the MonthlyTimesheet2 also that has a criteria. How can I modify the following:

1. Tardiness - to display the minutes or hours late instead of Text
2. Sick Leave - equivalent to 8 hrs/day if it's certified to be added to monthly hours
3. Absence - equivalent to 8 hrs/day deduction in hours format
4. Unpaid Leave - equivalent to 8 hrs/day deduction in hours format
5. How to change the default weekend to Thur/Fri or Fri/Sat

(Absence and Unpaid Leave though not paid, these are records only to be used as basis for performance evaluation)

Thanks again for all your valuable inputs.