This has been very challenging for me. I need to create a table that calculates the monthly amount for a given cost schedule (Table 1) and for a given set of due dates (Table 2).
Basically from something like this:
| Record ID | Start Date | End Date | Amount Due | Schedule Type |
|---|
| ZZZ | 1/1/2018 | 12/31/2018 | 500 | Quarterly |
I need to create this:
| Record ID | Payment Date | Amount Paid |
|---|
| ZZZ | 1/1/2018 | 500 |
| ZZZ | 4/1/2018 | 500 |
| ZZZ | 7/1/2018 | 500 |
| ZZZ | 10/1/2018 | 500 |
DATA PROVIDED:
Table 1 shows the Start Date and End Date of the amount due for different records. The Schedule Type tells whether it is “Monthly” or “Quarterly”. So for example 100ABC there is a cost of 500 every month from 9/1/2017 to 3/31/2018. For 200AAA, there is a cost of 300 every quarter from 4/1/2017 to 3/31/2018. [Schedule Type] is also what links Table 1 to Table 2.
Table 2 shows the actual Due Dates. The Due Dates are in the format of MM/DD. So “Monthly” is simply 1st day of each month for each year until the End Date. For Quarterly_type01 it is 01/01 (Jan 1), 04/01 (April 1), 07/01 (July 1), 10/01 (October 1) of each year until the End Date. Quarterly_type02 has specific dates also.
REQUIRED OUTPUT:
So what I was trying to do at first is create a list of dates based on those Due Dates by attaching a year (starting from perhaps 2016 until 2020). Then from the data on Table 1 cost schedule, I have to split the schedule and map them onto the Due Dates.
So far I have been failing miserably. What makes it even trickier is when the Start Date or End Date does not line up properly with the Due Dates. Record ID=300DEF and Record ID=400MMM are good examples. For those instances the amount for the partial month (or partial quarter) has to be prorated based on the number of days.
The tab EXPECTED OUTPUT on the attached excel file is the desired generated table. 3 fields… [Record ID], [Payment Date], [Amount Paid]. [Payment Date] is basically just the Due Dates attached to a year. Any guidance?
