As the question doesn't define precise business rules, you will almost certainly need to tweak these suggestions. Note that I've used F13 here, total hours, as it's unlikely that holiday working will be eligible for overtime and holiday premium, and I suspect that overtime is a simple calculation of TimeOut - TimeIn - Breaks - DailyTimeToWork (which is a constant set elsewhere). You can simply add them together provided that your holiday list does not contain Sundays. Thus the day is a Sunday if WEEKDAY(A13)=1 and a holiday if VLOOKUP(A13,Holidays,1,FALSE) is not an error.Ĭell I13 will need to contain a conditional which combines these two tests. VLOOKUP has a particular quirk in that it will return #N/A if a value is not found, so you need to need to test for the error. Then, your calculation needs to determine whether the column A date occurs in that range. You will get a more comprehensive view of labor costs. It will calculate payroll in detail including bonuses, and basic salary after deduction of leaves. You could set up a list of holidays on another sheet, and give that list a range name (say Holidays). Employee Payroll Calculator Excel Template: This is the well-organized template for labor cost calculation. You also need to include public holidays, which Excel doesn't cater for natively because every country's holidays are different. That can be done with the WEEKDAY() function, which returns 1 for a Sunday. The holiday hours needs to determine whether column A is a Sunday. That can be done with a custom format of DDD dd/mm. I'm going to assume column A is a real date value, formatted to show the day name as well.
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |