I want to calculate salaries of employees every month, I have annual pay package and the month of joining as the data source. So, I want to automate this process.

Edge Cases: 1. Suppose an employee joins on the 16th day of the month. And as per the norms I have to give him salary for the 14 days only. Now I can give him salary by the end of the joining month or on the end of next consecutive month. The consecutive month salary will be addition of 14 days salary and the following month salary.

Anyone have any idea of implementing this auto calculation?

I think hereâ€™s a logic to get you going. I assume you will pay â€śpartial salaryâ€ť on the same month.

1st part: The date templates.

Create a column to calculate the â€śjoined monthâ€ť of the employee.

Create a column to calculate the â€śjoined yearâ€ť of the employee.

Create a template to join the â€śjoined monthâ€ť and â€śjoined yearâ€ť. Letâ€™s say â€ś3 - 2022â€ť.

Create a column to calculate the â€ścurrent monthâ€ť.

Create a column to calculate the â€ścurrent yearâ€ť.

Create a template to join the â€ścurrent monthâ€ť and â€ścurrent yearâ€ť. Letâ€™s say â€ś4 - 2022â€ť.

2nd part: The salary calculation.

Create a math column to calculate the â€śDays in joined monthâ€ť number. Letâ€™s say if itâ€™s April 2022 then 30.

Create a math column to calculate the â€śDate joinedâ€ť number. Letâ€™s say if 15 April then 15.

Create a math column to calculate the â€śDays paid in joined monthâ€ť number. Take the â€śDays in joined monthâ€ť number and subtract it from the â€śDate joinedâ€ť number, plus 1. Letâ€™s say if you join on 30 April youâ€™ll be paid 30 - 30 + 1 = 1 day. This doesnâ€™t take into account the complexity of working and non-working days.

Create a math column to calculate the â€śDays in current monthâ€ť number.

Create a math column to calculate the â€śSalary in joined monthâ€ť number, take the daily salary and times it by the â€śDays paid in joined monthâ€ť number.

Calculate the â€śSalary in current monthâ€ť number, take the daily salary and times it by the â€śDays in current monthâ€ť number.

Finally, create an If Then Else column. If the two templates in the first part matches (meaning the employee joined this month), then return the â€śSalary in joined monthâ€ť number, else return the â€śSalary in current monthâ€ť number.