How to calculate salary in parts

[jalals]

Hi
I need a help to find out the Salary. A school remains closed for Winter vacation from 15^th Dec. of Current year to 28-29 Feb next year.)
The management disburse the salary to the school as per following procedure
If the Employee has not completed 6 months period (180 days) as on 15^th December of current year he will not be entitled to winter salary (2 and a half months) from 16^th Dec. of current year to Last day of Feb. next year. For rest of the period he will get normal salary. i.e. for 11 days of June and July, August, Sept. Oct, Nov and 15 days of Dec. of current year he will get full salary.
To elaborate it further it means that if an employee has been appointed on 19^th June of the current year his winter salary with effect from 16^th Dec. of current year to last day of Feb next Year will not be disbursed. For other time period he is entitled for normal salary.


Please helf to find out the solution.
Thanks
Jalal

[jzwp11]

You did not say where you wanted to do this calculation, so I will assume that you want to do it via a query. I will also assume that you have an employee table similar to the following

tblEmployee
-pkEmpID primary key, autonumber
-txtFName
-txtLName
-dteAppoint (appointment date of the employee)


I also assume that you would run the query to find out which employees are eligible or not sometime in December. With those assumptions in mind, the query might look like this


SELECT txtFName & " " & txtLName as EmployeeName, IIF(datediff("d", dteAppoint, dateserial(year(date()), 12, 15))>=180, "Eligible","Not Eligible") as WinterSalaryEligibility
FROM tblEmployee

The datediff() function finds the difference in days between the dteAppoint and the date that is created using the dateserial() function. The dateserial function creates a date with month =12 day=15 and extracts the year from the current date to get the date 12/15/xxxx for the current year. The IIF() function tests whether the difference in dates is >=180 days or not, if it is then the employee is eligible for the winter salary, if it is less than 180 days the employee is not eligible.

[jalals]

Hi
Thanks for the help
Your following formula was of tremendous use.

SELECT txtFName & " " & txtLName as EmployeeName, IIF(datediff("d", dteAppoint, dateserial(year(date()), 12, 15))>=183, "Eligible","Not Eligible") as WinterSalaryEligibility
FROM tblEmployee

You had rightly guessed my problem. The result was fine but one problem occurred.
Let me clarify the winter salary
(Winter salary = Last 16 days of December (current year) + 31 days of January (next year) + 28 or 29 days of February (next year))
It means that if an employee was appointed on 16 June Current Year, he will not be eligible for winter salary as he will not complete 183 days as on 15^th December (current Year).

The employee who has not completed 183 days service as on 15^th December, will be entitled only for 15 days salary for the month of December and Not entitled for salary for Winter Salary.
My table is as under
EmployeeId (pk) Autonumber
FirstName Text
BasicPay Number
Hiredate Date
With the above formula I could get the salary of 15 days for the month of December.

I created 3 fields in the query named as (1) “DueforWintersalary” (2) FindMonth (3) GrossSalary wherefrom I generated the following formulas:
DueforWintersalary: iif((DateDiff("d",[hiredate],DateSerial(Year(Date()),12,15)))<183,”NotEligible ”,”Eligible”))
FindMonth:Format(Date(),”mm”)
GrossSalary:IIf([Findmonth]<>12,([Basicpay]+[Specilallowance]),IIf(DueforWintersalary=”NotEligible”,([Basicpay]+[specialallowance])*15/31,([Basicpay]+[Specialallowance])))
(This proved correct for December (current year) salary. I tried a combined formula for January (next year) and (February next) salary as under

DueforWintersalary: iif((DateDiff("d",[hiredate],DateSerial(Year(Date()),2,-47)))<183,”NotEligible”, iif((DateDiff("d",[hiredate],DateSerial(Year(Date()),1,-16)))<183,”NotEligible”, iif((DateDiff("d",[hiredate],DateSerial(Year(Date()),12,15)))<183,”NotEligible ”, “Eligible”))))))
GrossSalary: IIf([Findmonth]<>2,([Basicpay]+[Specialallowance]),IIf([DueforWintersalary]=”NotEligible”,0, IIf([Findmonth]<>1,([Basicpay]+[Specialallowance]),IIf([DueforWintersalary]=”NotEligible”,0, IIf([Findmonth]<>12,([Basicpay]+[Specilallowance]),IIf(DueforWintersalary =”NotEligible”,([Basicpay]+[specialallowance])*15/31,([Basicpay]+[Specialallowance])))
This formula does not work in a combined manner
However, for individual months December (Current year), Jan. and Feb. Next year the individual formulas work correctly. But I want only one formula to be executed on at the end of the month to disburse the salary.
Salary for January Next
DueforWintersalary: iif((DateDiff("d",[hiredate],DateSerial(Year(Date()),1,-16)))<183,”NotEligible”,”Eligilbe”))
GrossSalary: IIf([Findmonth]<>1,([Basicpay]+[Specialallowance]),IIf([DueforWintersalary]=”NotEligible”,0,” ([Basicpay]+[Specialallowance])))
And same way Fabruary next
DueforWintersalary: iif((DateDiff("d",[hiredate],DateSerial(Year(Date()),2,-47)))<183,”NotEligible”,”Eligilbe”))
GrossSalary: IIf([Findmonth]<>2,([Basicpay]+[Specialallowance]),IIf([DueforWintersalary]=”NotEligible”,0,” ([Basicpay]+[Specialallowance])))

Can I generate one combined formula.
Please help

[jzwp11]

What data does the BasicPay field hold? Does it represent a daily salary, a monthly salary or a yearly salary.

Is your ultimate goals to produce a total yearly salary amount that the employee is entitled to taking into account whether they are eligible for the winter salary or not?

[jalals]

Hi
Actually the school disburses the salary to its employees on monthly basis. i.e. on the last day of every month the salary is disbursed even in winter season. The school remains closed for educational activities only during the winter session. But as a rule the staff should get the salary even if the school remains closed for the winter season. the staff should get the salary.

I have to develop the software where the system first finds out that (on the day of salary disbursement) which month is it. If system finds that the Month number is 03 (March) then all the employees are entitled for the salary for full month. likewise if month number is 04 (April), 05 (May) ..... 11 (November) then there will be no deduction in the salary of any employee even to the 183 days old (as on 15th December of any year). My column "FindMonth" in the query will serve this purpose.
On last day of the month I give a command to execute the query and the system calculates the salary get the printout and disburse accordingly.

I hope that you got me.
Thanks
Jalal

[jalals]

Hi
Let me clarify a little more.
BasicPay represents the salary for one Month
1. As the School suspends ‘academic activities’ during “Winter Session” (Winter Season is the period from 16^th December of the Current year to End of February Next Year), it has set the criteria for releasing/disbursing Salary to its employees as under.
2. Even though School remains suspended for academic activities during “Winter Season”, the Salary is disbursed to its employees on monthly basis including “Winter Season”. I.e. the employees get the salary for full year.
3. Yearly increment in Salary to the extent of 10% of basic Pay is disbursed to all the employees with the salary of January every year. i.e. every employ’s salary gets enhanced in the month of January. However, there is a condition.

a. Salary for the months of December (current Year), January (next Year) and February (next Year) will be disbursed to any employee subject the Employee has completed 183 days (6 month service) as on 15^th December of (current) Year on following terms. Let us call the employee with less than 6 months service as on 15^th December of current year as “Temporary employee”

i. The temporary employee is entitled for 15 days salary for the month of December (current year) as the school suspends ‘Academic Activities’ he will not, as such, work for 2 and a half months of winter season (16 days of December and January Next and February next).
ii. The temporary employee is not entitled for any salary for the months of January and February.
iii. The Temporary employee is also not entitled for the increment in January.
b. From March(next) the salary is disbursed as normal
Example: Suppose the school appoints an employee (say A) on 16^th June 2012. Under normal circumstance A will get 14 days salary for June 2012. From July 2012 to Nov. 2012 A will get normal salary. For December 2012, A will be entitled for 15 days salary. (As he will just complete 182 days service as on 15^th December 2012 when the school suspends academic activities).
Further, the rule says that A will not be entitled for the salary of January 2013 and February 2013, nor will he be entitled for Increment in January 2013. On 15^th December 2013 his service will be 547 days so he will automatically be entitled for all the salary benefits.

I have created a query with the following fields
1. BasicPay – represents pay for the month
2. NewBasicPay: CSng(Nz([basicpay]+[increment],0))
After the date of Increment I update the query (using update Query method) to update the Basic Pay.
3. HireDate – represents date of appointment of the employee
4. ServiceDays: Date()-[hiredate]
5. DaysofCurrentMonth: Format(Date(),"dd")
6. PartlySalary: [ServiceDays]/[DaysofCurrentMonth]
7. Findmonth: Format(Date(),"mm")
I use the field No.s 3,4,5,6,7 to determine the partly salary for a particular month as the employee will get the salary on number of days her served the School.
8. DueforWintersalary: iif((DateDiff("d",[hiredate],DateSerial(Year(Date()),12,15)))<183,True,False))
I use Field No. 8 to determine the Salary for December.
9. IncrementDue: IIf((DateDiff("d",[hiredate],DateSerial(Year(Date()),1,-16)))<183,False,True)
Use the field No.9 to determine whether the employee is entitled for increment. (Temporary Employee)
10. Increment: IIf([Is it Increment Day]<>"Y",0,IIf([IncrementDue]=True,IIf([ReleaseIncrement]=True,[basicpay]*0.1,0)))
Field No. 10 disburses the Increment to the eligible employee in January.
11. GrossSal: CSng(Nz(IIf([partlySalary]<1,(([newbasicpay]+[spallow])*([ServiceDays])/([DaysofCurrentMonth])),IIf([Findmonth]<>12,([newBasicpay]+[Spallow]),IIf([DueforWintersalary]=True,([newbasicpay]+[spallow])*15/31,([newBasicpay]+[Spallow]))))))

From field no. 1 to 11 I determine the General salary of the employee from march to December.

In place of field No. 8 which fiends salary for December, I use the following formula to find out January Salary. iif((DateDiff("d",[hiredate],DateSerial(Year(Date()),1,-16)))<183,true,false))

February salary iif((DateDiff("d",[hiredate],DateSerial(Year(Date()),2,-47)))<183,true,false))
This way salary is calculated using 3 formulas. (December, January, February)

But I want that the salary should be calculated using only one formula i.e. combining three in one.

Thanks for patience
jalal

[jzwp11]

But I want that the salary should be calculated using only one formula i.e. combining three in one.

Although it is possible to do this in one calculation, the number of nested IIF() functions gets very difficult to work with. For that reason, I decided to construct a custom function in code. That function called GetMonthlySalary() takes into account if the employee is a new employee and has not reached 183 days of service by 12/15 of the year in which they were hired & their eligibility for winter pay as you have described. It also takes into account, the partial month's pay in the month of hire if the date of hire is not the first of the month. Hopefully I have done everything in line with what you need. Also, I have added comments within the code to help explain each step of the code in case you need to modify it to fit your needs.

Also, you mention that the salary changes from one year to the next, that also adds another complexity which has to be dealt with in your table structure. Since a person can have many salaries (over time), that is a one to many relationship, so you need a table to hold those salaries. You would remove the salary field from your employee table and create a new, related table that would look like this:

tblEmployeeSalary
-pkEmpSalaryID primary key, autonumber
-fkEmployeeID foreign key to tblEmployees
-dteEffective (effective date of the salary)
-currBasicPay (the salary)

The other thing you mentioned is that you will need the salary for each employee for each month (and year), so that requires 2 more tables (one for year and one for months) and some additional queries to bring things together.

In the attached database, you will find the custom function, queries and tables I discuss above. The query you will want to focus on for getting the monthly salary is qryYearMonthEmpSalary. That query uses the custom function I created.