Hi all... I hope you're all in a great health.
I'm faced with a problem...
I lost my head thinking about this while doing this manually. So I have a monthly contribution for 1000+ employees dues that must be paid to vendor on either 2nd or 14th every month. But here's the thing:
- If the starting date for the employee is on 2nd of the month, then the payment must be paid at the latest on the 2nd of the next month. For example, if the employee starts working on January 2nd, then the payment contribution must be paid at the latest on February 2nd. But there are many starting dates each month -- for example there are new employees on the 2nd, 15th, 20th of January.
- It's a pain to make adhoc payments more than twice each month. So there will be a maximum two payments made each month, on the 2nd and 14th.
- Since there are many starting dates and only two payment dates, we must group the other various dates into either the 2nd or 14th payment according to the closest payment date. For example: starting date 15th and 20th January will be paid in advance on the payment day 14th February. Starting date 1st January will be paid on payment day 2nd January.
I have attached the sample of excel...
The 'List' sheet is infinite. It should calculate as many employees as possible
There are two kinds of contributions must be paid and it's based on the salary of the employee. I used VLOOKUP to determine the amount. The 'Total Contribution' is what each employee has to pay.
There will be a 'Calculate' button in 'List' sheet that would calculate and determine if the employees' contribution would be paid on either 2nd or 14th of the next month. The result would show in 'Details' sheet
There are salary changes, so the salary in 'List' sheet would change based on the salary changes inputted in 'Salary Change' sheet. I already made a dropdown list but can't quite make the IF function to show the salary based on the 'Salary Change' sheet... According to the month the list shows, the 'Calculate' button should be pressed and the correct amount shown in 'Details' sheet
I'd really appreciate it if any of you could help me... Have a good day!
Bookmarks