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.