asked on
Need help with two formuls for determining what deduction effective date should be used in our HR software.
I have a list of 50+ companies and the corresponding pay period begin dates, pay period end dates and check dates for each pay period in 2016. From this list I am trying to determine what insurance deduction "begin" date should be entered in our HR software.
I have attached a worksheet containing a template where I would like HR to enter their company name, and the day an employee's BCBS insurance policy is effective. From these two pieces of information, I would like the "deduction begin date" to appear.
Here is an example: Company is "Sardis" - BCBS ins policy is effective 4/1/2016. We review the pay period begin date column and look for the first instance of April 2016. Then grab the check date in the last column. This date would be populated on the template. In this case, based on the data in the attached worksheet, the date would be 4/29/2016.
I also need a similar formula for our AFLAC deductions. In this case, the date we want populated is always the first check date that is in the Month/Year of the insurance effective date So, if the policy is effective 4/1/2016, we would look for SARDIS, then for the first check written in April 2016. That's the date I would want populated in the template. In this case, it would be 4/1/2016.
Payroll-PAYPERIODREPORTBYCALENDARYEA.xls
I have attached a worksheet containing a template where I would like HR to enter their company name, and the day an employee's BCBS insurance policy is effective. From these two pieces of information, I would like the "deduction begin date" to appear.
Here is an example: Company is "Sardis" - BCBS ins policy is effective 4/1/2016. We review the pay period begin date column and look for the first instance of April 2016. Then grab the check date in the last column. This date would be populated on the template. In this case, based on the data in the attached worksheet, the date would be 4/29/2016.
I also need a similar formula for our AFLAC deductions. In this case, the date we want populated is always the first check date that is in the Month/Year of the insurance effective date So, if the policy is effective 4/1/2016, we would look for SARDIS, then for the first check written in April 2016. That's the date I would want populated in the template. In this case, it would be 4/1/2016.
Payroll-PAYPERIODREPORTBYCALENDARYEA.xls
Microsoft Excel
Last Comment
Subodh Tiwari (Neeraj)