List of Salary Change into Pro=rated Salary from Apr 2018 to Mar 2019

[moodyjack]

Hi,

I have a table (A to D) showing salary changes for staff. Would like to convert it into salary (pro-rated if needed) for 12 months from Apr 2018 to Mar 2019 (E to G). Please help!


[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 1089"]
<colgroup><col span="2"><col><col><col span="2"><col><col><col></colgroup><tbody>[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD][/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Staff No.[/TD]
[TD]Salary[/TD]
[TD]Reason[/TD]
[TD]Effective Date[/TD]
[TD][/TD]
[TD]Staff No.[/TD]
[TD]Payroll Month[/TD]
[TD]Amount[/TD]
[TD]Formula (For Reference)[/TD]
[/TR]
[TR]
[TD]A001[/TD]
[TD="align: center"]8000[/TD]
[TD]Annual Review[/TD]
[TD="align: right"]1-Jan-18[/TD]
[TD][/TD]
[TD]A001[/TD]
[TD="align: right"]Apr-18[/TD]
[TD] 8,000.00[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]A001[/TD]
[TD="align: center"]9000[/TD]
[TD]Annual Review[/TD]
[TD="align: right"]1-Jan-19[/TD]
[TD][/TD]
[TD]A001[/TD]
[TD="align: right"]May-18[/TD]
[TD] 8,000.00[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]A002[/TD]
[TD="align: center"]10000[/TD]
[TD]New Hire[/TD]
[TD="align: right"]22-May-18[/TD]
[TD][/TD]
[TD]A001[/TD]
[TD="align: right"]Jun-18[/TD]
[TD] 8,000.00[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]A002[/TD]
[TD="align: center"]11000[/TD]
[TD]Probation passed[/TD]
[TD="align: right"]22-Aug-18[/TD]
[TD][/TD]
[TD]A001[/TD]
[TD="align: right"]Jul-18[/TD]
[TD] 8,000.00[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]A002[/TD]
[TD="align: center"]12500[/TD]
[TD]Annual Review[/TD]
[TD="align: right"]1-Jan-19[/TD]
[TD][/TD]
[TD]A001[/TD]
[TD="align: right"]Aug-18[/TD]
[TD] 8,000.00[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]A002[/TD]
[TD="align: center"]12500[/TD]
[TD]Resignation[/TD]
[TD="align: right"]15-Feb-19[/TD]
[TD][/TD]
[TD]A001[/TD]
[TD="align: right"]Sep-18[/TD]
[TD] 8,000.00[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]A001[/TD]
[TD="align: right"]Oct-18[/TD]
[TD] 8,000.00[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]A001[/TD]
[TD="align: right"]Nov-18[/TD]
[TD] 8,000.00[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]A001[/TD]
[TD="align: right"]Dec-18[/TD]
[TD] 8,000.00[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]A001[/TD]
[TD="align: right"]Jan-19[/TD]
[TD] 9,000.00[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]A001[/TD]
[TD="align: right"]Feb-19[/TD]
[TD] 9,000.00[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]A001[/TD]
[TD="align: right"]Mar-19[/TD]
[TD] 9,000.00[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]A002[/TD]
[TD="align: right"]Apr-18[/TD]
[TD] - [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]A002[/TD]
[TD="align: right"]May-18[/TD]
[TD] 3,225.81[/TD]
[TD]Formula is 10,000 x (31-21)/31[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]A002[/TD]
[TD="align: right"]Jun-18[/TD]
[TD] 10,000.00[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]A002[/TD]
[TD="align: right"]Jul-18[/TD]
[TD] 10,000.00[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]A002[/TD]
[TD="align: right"]Aug-18[/TD]
[TD] 10,322.58[/TD]
[TD]Formula is 10,000 x 21/31 + 11,000 x (31-21)/31[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]A002[/TD]
[TD="align: right"]Sep-18[/TD]
[TD] 11,000.00[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]A002[/TD]
[TD="align: right"]Oct-18[/TD]
[TD] 11,000.00[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]A002[/TD]
[TD="align: right"]Nov-18[/TD]
[TD] 11,000.00[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]A002[/TD]
[TD="align: right"]Dec-18[/TD]
[TD] 11,000.00[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]A002[/TD]
[TD="align: right"]Jan-19[/TD]
[TD] 12,500.00[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]A002[/TD]
[TD="align: right"]Feb-19[/TD]
[TD] 6,250.00[/TD]
[TD]Formula is 12500 x 14/28[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]A002[/TD]
[TD="align: right"]Mar-19[/TD]
[TD] - [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]