Showing the actual dates of overlap instead of number of days overlapped

[Lynx_TWO]

Good Evening!

I am trying to show actual dates of overlap, then add amounts based on multipliers in the same row. I think I have most of the building blocks, I just need to figure out how to link it all together...

[TABLE="width: 1466"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[TD]O[/TD]
[TD]P[/TD]
[TD]Q[/TD]
[/TR]
[TR]
[TD]BEGINNING DATE[/TD]
[TD]ENDING DATE[/TD]
[TD]Monthly Pay Rate[/TD]
[TD]Beginning Date as text[/TD]
[TD]Ending Date as text[/TD]
[TD]Actual Days Between Dates[/TD]
[TD]Months Between Dates[/TD]
[TD]No-Pay Days in 1st Month (based on 30 days in a month)[/TD]
[TD]No-Pay Days in Last Month (based on 30 days in a month)[/TD]
[TD]Pay Days in 1st Month (based on 30 days in a month)[/TD]
[TD]Pay Days in Last Month (based on 30 days in a month)[/TD]
[TD]Monthly Rate Multiplier[/TD]
[TD]Monthly Pay[/TD]
[TD]Overlapping Dates? (In Columns D & E)[/TD]
[TD]No-Pay Dates in Columns D & E[/TD]
[TD]If overlapping dates, Add multipliers in column L for only the overlapping dates, never going above 1[/TD]
[TD]Show resulting pay schedule for the 1st of each month[/TD]
[/TR]
[TR]
[TD="align: right"]1/11/2016[/TD]
[TD="align: right"]3/18/2016[/TD]
[TD="align: right"]$5,875.00[/TD]
[TD]01/11/2016[/TD]
[TD]03/18/2016[/TD]
[TD="align: right"]67[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]0.25[/TD]
[TD="align: right"]$1,468.75[/TD]
[TD="align: center"]TRUE[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1/11/2016[/TD]
[TD="align: right"]5/7/2016[/TD]
[TD][/TD]
[TD]01/11/2016[/TD]
[TD]05/07/2016[/TD]
[TD="align: right"]117[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]0.75[/TD]
[TD="align: right"]$4,406.25[/TD]
[TD="align: center"]TRUE[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6/11/2016[/TD]
[TD="align: right"]7/8/2016[/TD]
[TD][/TD]
[TD]06/11/2016[/TD]
[TD]07/08/2016[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]0.75[/TD]
[TD="align: right"]$4,406.25[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: right"]34[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1/11/2016[/TD]
[TD="align: right"]5/6/2016[/TD]
[TD][/TD]
[TD]01/11/2016[/TD]
[TD]05/06/2016[/TD]
[TD="align: right"]116[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]0.25[/TD]
[TD="align: right"]$1,468.75[/TD]
[TD="align: center"]TRUE[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5/12/2016[/TD]
[TD="align: right"]6/8/2016[/TD]
[TD][/TD]
[TD]05/12/2016[/TD]
[TD]06/08/2016[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]$5,875.00[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


OK, so Column A and B have the starting and ending dates.

Column C in the monthly rate, this could be any number

The following Formulas are listed as per column:

Column D3:

=TEXT(A3,"mm/dd/yyyy")

Column E3:

=TEXT(B3,"mm/dd/yyyy")

Column F3:

=DATEDIF(D3,E3,"d")

Column G3:

=IF(ISERR(F3),"NA",DATEDIF(D3,E3,"m"))

Column H3:

=IF(ISERR(F3),"NA",(MID(D3,4,2))-1)

Column I3:

=IF(ISERR(F3),"NA",30-(MID(E3,4,2)))

Column J3:

=IF(ISERR(F3),"NA",30-H3)

Column K3:

=IF(ISERR(F3),"NA",30-I3)

Column L3: This is entered in manually

Column M3:

=IF(ISERR(F3),"NA",PRODUCT($C$3,L3))

Column N3:

=SUMPRODUCT((D3<$E$3:$E$7)*(E3>=$D$3:$D$7))>1

Column O3:

=IF((ISERR(F3)),VALUE(0),IF(N3=FALSE,((D3-E2)-1),VALUE(0)))

Now what I need to do if figure out the formula needed to show the actual dates that are overlapping, starting and ending. This could be two other columns (P and Q, for example) as right now there doesn't need to be one formula to do all of this, and I have to think about the problem one step at a time.

Next, I need to add the rate multipliers that correspond with the overlapping dates, in a separate column is fine.

Finally, I need the spreadsheet to figure out what the corresponding amount of pay would be for a first-of-the-month paycheck.

For example:

Given the dates:

[TABLE="width: 152"]
<tbody>[TR]
[TD]01/11/2016[/TD]
[TD]03/18/2016[/TD]
[/TR]
[TR]
[TD]01/11/2016[/TD]
[TD]05/07/2016[/TD]
[/TR]
[TR]
[TD]06/11/2016[/TD]
[TD]07/08/2016[/TD]
[/TR]
[TR]
[TD]01/11/2016[/TD]
[TD]05/06/2016[/TD]
[/TR]
[TR]
[TD]05/12/2016[/TD]
[TD]06/08/2016[/TD]
[/TR]
</tbody>[/TABLE]

From 01/11/2016 to 03/18/2016, the Monthly Rate Multiplier would be 0.25+0.75+0.25=1.25, times the Monthly Pay Rate in C2, divided by 30, then times the number of days in each month, so:

On 02/01/2016, pay would be (1.25*$5,875.00)/30 (for the daily rate) then multiplied by 20 (pay days in the first month)

So, on 02/01/2016, pay would be $4,895.83

On 03/01/2016, pay would be (1.25*$5,875.00)/30 (for the daily rate) then multiplied by 30 (pay days in the first month)

So, on 03/01/2016, pay would be $7,343.75

From 03/01/2016 to 03/18/2016, the Monthly Rate Multiplier would be 0.25+0.75+0.25=1.25, times the Monthly Pay Rate in C2, divided by 30, times 18 days in the month. Then, the Monthly Pay Rate changes, so from 03/19/2016 to 05/06/2016, Monthly Rate Multiplier would be 0.75+0.25=1.00, times the Monthly Pay Rate in C2, divided by 30, multiplied by the remaining days in the month (30-18=12)

So, on 04/01/2016, pay would be $4,406.25+$2,350 = $6,756.25

I would like to keep this one step at a time in individual cells, as it seems like this should be simple, but I can't seem to wrap my head around this...

Thanks so much in advance!

 

[Lynx_TWO]

Reading this, I think I'm asking too much all at once, so I guess it would be easier if I broke it down into steps...

Step 1. Find the start and end dates of overlapping dates in an array.
Step 2. Find which rows are involved in each range of overlapping dates,
Step 3. Add the multipliers in the rows involving the overlapping dates.
Step 4. - I think I could probably take it from there, but I'd need the first three steps to visualize the formulas needed... My brain tends to move in a very serial direction unfortunately, hence breaking out everything into multiple rows of cells