View Single Post
 
Old 01-14-2016, 04:25 AM
muraterguden muraterguden is offline Windows 7 64bit Office 2013
Novice
 
Join Date: Jan 2016
Posts: 3
muraterguden is on a distinguished road
Default Help needed! IF, SUMIFS, INDEX-MATCH or anything else???

Hi all,
I have a payroll file made with several sheets. I store almost all data in the sheet I named Main DB. In this sheet mostly the working hours and some other attendance exceptions are calculated and converted to 0:00 time format. Then in other sheets dedicated to each month, the relevant info is extracted then the Gross and Net pay calculations are done.
One of those exceptions mentioned above is the Maternity Leave (Letter M is used for coding as shown in Image 1). By local laws, employer must pay 98 days wage to the employee if qualified. The first 42 of 98 days must be paid in full (pay for 8 hours work) and the remaining days in half (pay for 8 hours work).
Image 1
https://drive.google.com/file/d/0B5sxgCATcKItVjNPZGtNSWp2Yms/view?usp=sharing
Normally, I subtract 2 different employee contributions (named NIS and H/S) for social security from the Gross to find the Net pay. (See the Image 2) However, if employee has a Gross pay generated from Maternity code "M", I do not deduct those employee contributions and pay the whole Gross.
Image 2
https://drive.google.com/file/d/0B5sxgCATcKItdGZPc2xhaDBxQVk/view?usp=sharing
I need a function to return $0:00 for the NIS value of the days marked with "M"
For now, my NIS calculation is just a LOOKUP which looks for the Gross value in a table stored in another worksheet. I need some expert touch at this point.
Thank you all in advance.