Add a MONTHDIF function
The formula should work like the YEARFRAC function. It should calculate partial months on either end of the dates. So starting 1/15/20 should calculate as 16/31 days or 0.516.. months, and ending at 6/5/20 should calculate as 25/30 day or 0.8333.. months. Currently, I have to use a ridiculously long formula to achieve this.
joseph st-matc commented
I want to add Riban to my task bad
Miss anonymous you really need to get your own Outlook account you have posted several several times on my account I will go forward to get you off my account anyway I can
It'd be wonderful for loan interest type calculations. If it took an argument for "fraction of the month to the beginning or to the end":
MONTHFRAC( date, 1 if to the end of the month OR 0 if to the beginning of the month )
So a billing period's interest would be:
=( MONTHFRAC("7/29/19",1) + MONTHFRAC("8/27/19",0) ) * (RATE/12) * PRINCIPAL
and that'd allow easy adjustment for payments made in the period reducing principal for part of the period. Or interest and fees posted in the period. As with everything, there ARE substitute approaches that have stood the test of time, but the all suck due to complexity in the formulas. Besides, this place isn't about "dude, just do this complicated and unintuitive thing for the rest of your life." It's about what would improve things for a fair number of people.
This would be a great help when calculating partial month amounts.
Not sure how complicated the formula you use is, and what follows is more than neeeded since this ought to be a function that just needs provided a date, but the below will calculate it without being ridiculously long (objectively that is):
for a date entered in A1. (It will convert the output of each TEXT() function for the arithmetic, so no need to wrap them with VALUE() functions. But the TEXT() function ARE needed to extract the day of the month without obnoxious work with strings.)
Lisa Bridges commented
I vote for Add a MONTHFRAC function.
Marilynne Clark commented
Sorry, I meant to call the function MONTHFRAC.
What a great idea!