For calculating interest. Anything that's calculated as 30/365, for example, accumulates interest every day, except for the 31st of a month. I'd like to figure out a way to tell how often the 31st has occurred between two dates - for example, between March 15th 2017 and today, there have been 2 31st's - March 31 and May 31st. Is there a good way to figure this out in Excel besides listing out each month in the formula?
You can use this:
=SUMPRODUCT(--(DAY(EOMONTH(B1,ROW(INDIRECT("1:" & (MONTH(TODAY())-MONTH(B1))+12*(YEAR(TODAY())-YEAR(B1))))-1))=31))
What this does it returns the last day of each month between the desired date and today. If it equals 31 it counts it. The SUMPRODUCT counts each true as one and False as 0.
Thus it counts the months that end in 31.
For interest, even though there is an accepted answer, this will also work, and is a bit shorter:
It creates an array of all the dates from Start to End inclusive, and counts the number of days = 31.
This method will run into the dreaded
25 Nov 4770 problem, unless Excel has increased the number of rows by then :-)
You can use the fact that there are 7 31sts per year to your advantage. For start date in
B1 and end date in
- Calculate the difference in the years of the start and end dates, irrespective of the month. Multiply the difference plus 1 by 7. This will provide a base over-count of the 31sts from which we will subtract.
- Next, look up the number of preceding 31sts in the same calendar year as the start date. This is handled by the array which gives this value for each month. This value is subtracted from the total.
- Then, do similar for the end date, but instead look up the number of remaining 31sts in the same calendar year as the end date. This value is subtracted as well.
- Finally, correct for the corner case where the end date is the 31st of a month. In this case, add 1 to the total.
- Difference in years = 2016 - 2013 = 3 --> Add (3 + 1) * 7 = 28 to the number of 31sts.
- Lookup preceding 31sts in start date calendar year: September has 5 preceding 31sts. --> Subtract 5 from number of 31sts: 28 - 5 = 23
- Lookup remaining 31sts in end date calendar year: December dates have 1 remaining 31st. --> Subtract 1 from number of 31sts: 23 - 1 = 22
- Check for 31st end date: The end date is on the 31st, so add 1 to the number of 31sts: 22 + 1 = 23.