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,

 

[Akashwani]

Hi,

1. In cell J3 and copy down =IF(C3>$P$8,C3-$P$8,IF(E3<$P$6,$P$10-D3,""))

2,3,4 Did you want a running monthly total of each point or a total for all?
Where do you want this data displayed? Change the formula in Q12 to this and copy down =COUNTIF($K$3:$K$33,LEFT(P12,1))*$P$6

5. Take a look here and see if these help....

http://www.youtube.com/user/ExcelIsFun#p/search/8/YXBQpBv6Iv8
http://www.youtube.com/user/ExcelIsFun#p/search/0/CtrFnGWqoGE


Ak

 

[Bongards]

Thanks AK. Yes, just a running total of each point. You saved me weeks of playing around the formula to get the intended result.

I just need some finishing touches to match what they ask for by watching videos from ExcelIsFun.

Thank you very much for the kindness and helping hand.