Number of periods between two

MissPerformation

New Member
Joined
Nov 19, 2018
Messages
2
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).


Beg Date
1/1/2019

End Date
12/31/2019

Term (months)
12
=DATEDIF($D$3,$D$4+15,"m")

<tbody>
[TD="align: center"]Column C
[/TD]
[TD="align: center"]Column D
[/TD]
[TD="align: center"]Formula
[/TD]

</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:

Beg Date
1/25/2019

End Date
12/31/2019

Term (months)
11
=DATEDIF($D$3,$D$4+15,"m")

<tbody>
[TD="align: center"]Column C
[/TD]
[TD="align: center"]Column D
[/TD]
[TD="align: center"]Formula
[/TD]

</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!
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
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))
 
Last edited:
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,162
Messages
6,170,431
Members
452,326
Latest member
johnshaji

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top