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!

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

<tbody>
</tbody>