Number of periods between two

[MissPerformation]

Happy New Year!

I am trying to calculate the number of periods between 2 dates that will return the months and remaining days in a month if the beginning date is a mid-month convention.

For example, what I’ve been doing is the following (DatedIF function may not be the best for this).

Column C	Column D	Formula
Beg Date	1/1/2019
End Date	12/31/2019
Term (months)	12	=DATEDIF($D$3,$D$4+15,"m")

<tbody>
</tbody>

Once the periods in column D are calculated those periods then run into the following formula where SUM=total payments made during the contract divided by the number of periods. This smooths out the payments over the life of the contract (or Straight-line for the accounting folks).
=-(SUM($F$17:$F$256)/12

It gets tricky when the dates are mid-month:

Column C	Column D	Formula
Beg Date	1/25/2019
End Date	12/31/2019
Term (months)	11	=DATEDIF($D$3,$D$4+15,"m")

<tbody>
</tbody>

Here I would need the 11 periods but also need the remaining days “5” (see note below about days in a month). That flow-through formula would look like this:

=-(SUM($F$17:$F$256)/11 months & 5 days.

To make things even more complicated I’m running all of this as a test calculation against software that calculates all months as 30 days regardless of leap years, Feb. 28 days or 30 & 31 days. It just looks at a year as 360 days. In addition, I have the spreadsheet set-up so that all a person has to do is enter the dates and everything else auto populates.

Any help you can provide would be so much appreciated!

 

[Oaktree]

Perhaps:

Month calculation =DATEDIF(D3-(DAY(D3)>30),D4-(DAY(D4>30)),"m")

Day calculation =IF(DAY(D4)>DAY(D3),MIN(30,DAY(D4))-MIN(30,DAY(D3)),30-MIN(30,DAY(D3))+MIN(DAY(D4),30))

 

[MissPerformation]

Perhaps:

Month calculation =DATEDIF(D3-(DAY(D3)>30),D4-(DAY(D4>30)),"m")

Day calculation =IF(DAY(D4)>DAY(D3),MIN(30,DAY(D4))-MIN(30,DAY(D3)),30-MIN(30,DAY(D3))+MIN(DAY(D4),30))

I tried the above and the Month Calculation is still giving me the full periods - for example the =DATEDIF(D3-(DAY(D3)>30),D4-(DAY(D4>30)),"m") for dates 1/25/2019 - 12/31/2019 gives me 12 periods. In actuality it's 11 periods plus some days.

I think it might be because I've denoted it with "m", which makes sense for the formula.

Are you suggesting I combine the two formulas above to get a combo of months and days?

 

[Oaktree]

I tried the above and the Month Calculation is still giving me the full periods - for example the =DATEDIF(D3-(DAY(D3)>30),D4-(DAY(D4>30)),"m") for dates 1/25/2019 - 12/31/2019 gives me 12 periods. In actuality it's 11 periods plus some days.

Not sure I follow.

If D3 = 1/25/2019 and D4 = 12/31/2019 then =DATEDIF(D3-(DAY(D3)>30),D4-(DAY(D4>30)),"m") returns 11.