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).
<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:
<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!
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!