Daily Attendance Sheet of Employees for Gross Salary

Attached please find a worksheet which was created by me through Google help. I am a beginner in Excel's Formulas.
I received a project from my boss to make an attendance sheet of each employee separately on monthly basis
but it's not only an attendance sheet it's a whole program to show the gross salary of an employee after many deductions
and additions according to employee salary as shown under:

1- Attendance time : 10:00 AM if employee come 1 or 2 hours late so these hours wages are deducted.

2- Overtime Charges (8 hours are working hours after that over time charges starts) we also have to add his overtime charges.

3- If employee gone before completion of 8 hours so the whole day wage is deducted from the month salary.

4- Sometime employee come to Sunday for extra work bundle so we have to include his overtime charges of Sunday.

5- Our boss and his partner had two companies i) ITC & ii) OCEX If ITC's employee gave his (Overtime / ITC's Working hours)
to OCEX 's outdoor work so these hours wages should pay by OCEX Admin and this rule is also applicable on ITC's Admin so we
have to show also that how many charges should pay by ITC & OCEX in the end of the month of this employee who works for both
companies.

In this sheet i made above 4 points but with troubles as under:

1- I used countif formula in E38 to count LATE in Column H and in F38 I multiply E38 (Late Days) to C2 (Day Wage)
to deduct day wages. Late applies on H Column if Working hours less than Regular 8 Hours if employee gives 8
hours or above 8 hours so the H column says NO means Day wage Safe but on Sunday when employee give overtime
so we don't need his 8 hours my format deduct his Late on Sunday also how can i save his Sunday Overtime.

2- In Date column which contains "Sun," wording we have to gave this row (R:255 G:124 B:128) color except H Column.

3- I am not able to add above point #5 in this sheet because after too much hardness i made this sheet please help?????

Regards
Shoaib Ali

Hi there,

Take a look at the attached modified version of your workbook and see if it does what you need. It contains one new worksheet which you can use as a template for all of the other worksheets in your workbook.

You should enter data only in the cells which have blue backgrounds. Most of the other cells contain formulas and the new worksheet has been protected to safeguard these.

You will find that when you enter a value in the Month / Year cell, all of the daily dates in Column A will be automatically updated in accordance with the month and year specified. Rows which correspond to Sundays will be automatically highlighted.

There is a new cell which contains the Overtime Hourly Rate - this rate is applied to all hours worked on Sundays.

I have used Named Ranges for those cells which contain certain data - e.g. Basic Hourly Rate, Employee Name, Regular Hours etc. I have also used the Named Constants sNO and sLATE (which have values of NO and LATE). Using these Named Constants makes it easier to ensure consistency between the text values, the values used in the formulas, and the values used for Conditional Formatting of Column G.

I hope this helps - please let me know how you get on.

Regards,

Greg M

There is no Value shown in cells but #VALUE ERROR if you add any function so please send me maybe after that it's working
and My Point#5 still not resolved :

5- Our boss and his partner had two companies i) ITC & ii) OCEX If ITC's employee gave his (Overtime / Working hours)
to OCEX 's Works so these hours wages should pay by OCEX and this rule is also applicable on ITC so we have to show
also that how many charges should pay by ITC & OCEX in the end of the month of this employee who works for both
companies.

Thanks for your great struggle on this sheet.

The file is working when i don't click on enable editing. If i click on enable editing whole process shows value error...

Hi again,

The workbook functions correctly when using Excel 2000 and Excel 2010 on my system.

I'm sorry, but I don't understand what you mean when you say:

There is no Value shown in cells but #VALUE ERROR if you add any function

Also, which cells display the #VALUE! error message?

I don't receive any "enable editing" prompt when I open the workbook.

Regarding your Point #5 - if an employee can work for only one company on any one day it will be reasonably easy to include a "Company" field on each row, and to calculate how much each company needs to pay at the end of the month. If an employee can work for (e.g.) three hours for one company and (e.g.) five hours for the other company on the SAME day, the situation becomes a lot more involved! You will need to use extra columns to record how much time the employee worked for each company, which in turn will make the worksheet layout and the formulas involved more complicated.

Please let me know the situation regarding Point #5.

Regards,

Greg M

hi.. i need help . i want to know how i deduct absent from salary.. when employee do overtime. we gave additional half hour overtime. like when employee to 4 hrs overtime we give 4/2=2+4=6hr. so i wana know right method for absent deduction and late coming or early leaving. we deduct all from actual overtime. than remaining overtime left. we add half over time in it. for example. total lates and absents and early leaving 40 hr. and total overtime in month is 50 hrs. now we deduct 50-40=10 remaining overtime. now we add 5hr overtime = total overtime becomes 15hrs.
please tell me weather we do correct or wrong. and please sene me correct attendance sheet. thank you..
Regards
umar siraj