How to auto calculate values every month or using no. of days?

Ask for Help
1

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?

1 Like
2

So the end result you want is to view how much salary you have to pay each of your employees in the current month?

3

@ThinhDinh , yes

4

Is there any way to implement this :point_up_2: :point_up_2:

5

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.

3 Likes
6

Info on how to calculate a month’s number of days.

2 Likes