+ Reply to Thread
Results 1 to 6 of 6

Daily Attendance Sheet of Employees for Gross Salary

  1. #1
    Registered User
    Join Date
    03-21-2012
    Location
    Karachi, Pakistan
    MS-Off Ver
    Excel 2010
    Posts
    81

    Unhappy 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
    Attached Files Attached Files

  2. #2
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2013
    Posts
    3,759

    Re: Daily Attendance Sheet of Employees for Gross Salary

    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
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    03-21-2012
    Location
    Karachi, Pakistan
    MS-Off Ver
    Excel 2010
    Posts
    81

    Re: Daily Attendance Sheet of Employees for Gross Salary

    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.

  4. #4
    Registered User
    Join Date
    03-21-2012
    Location
    Karachi, Pakistan
    MS-Off Ver
    Excel 2010
    Posts
    81

    Re: Daily Attendance Sheet of Employees for Gross Salary

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

  5. #5
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2013
    Posts
    3,759

    Re: Daily Attendance Sheet of Employees for Gross Salary

    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

  6. #6
    Registered User
    Join Date
    11-17-2015
    Location
    karachi, Pakistan
    MS-Off Ver
    2013
    Posts
    1

    Re: Daily Attendance Sheet of Employees for Gross Salary

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Salary Bracket returns employees corresponding Contribution for philhealth
    By cmaesi in forum Excel Formulas & Functions
    Replies: 17
    Last Post: 01-30-2020, 01:09 AM
  2. From net salary to gross
    By blueocean84 in forum Excel General
    Replies: 12
    Last Post: 10-30-2013, 08:55 AM
  3. [SOLVED] Salary Bracket returns employees corresponding Contribution for philhealth
    By cmaesi in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 06-18-2013, 11:52 AM
  4. Net to gross salary
    By TK34 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 04-16-2013, 08:58 AM
  5. how to list the employees with respect to the salary brackets???
    By phantomneo in forum Excel Charting & Pivots
    Replies: 12
    Last Post: 11-13-2012, 03:24 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1