3

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?

  • Do you want to count the first or last dates if they fall on the 31st ?? – Gary's Student Jun 5 '17 at 16:59
  • The rule is, for interest that's 30/360, 30/365, etc., the 31st is ignored. As a result, we need to count all 31st that are in the range. This includes the first and the last date if the fall on the 31st. – Selkie Jun 5 '17 at 19:59
  • On the other side, what about February? Are you making a separate adjustment for that month? – gns100 Jun 5 '17 at 20:21
  • No, February gets no special adjustment. – Selkie Jun 5 '17 at 21:07
3

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.

![enter image description here

  • This is beautiful! Going to test it for corner-cases – Selkie Jun 5 '17 at 19:56
  • I'm getting a #REF error when trying this out - it's unhappy with the indirect row - specifically, this is occurring when there are 0 instances of 31 day months between the dates. See if I can trigger it other ways – Selkie Jun 6 '17 at 21:14
  • 1
    Yup, only on no entries. Wrap in an iferror(equation,0) to fix. – Selkie Jun 6 '17 at 21:20
2

For interest, even though there is an accepted answer, this will also work, and is a bit shorter:

=SUMPRODUCT(N(DAY(ROW(INDIRECT(StartDate &":"&EndDate)))=31))

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

  • This looks really promising, I'll take a look! – Selkie Jun 6 '17 at 22:22
  • Yup it is shorter, but it will do 30 times more iterations than mine. :) – Scott Craner Jun 7 '17 at 1:08
  • @ScottCraner Not sure that would be an issue given various date ranges and the increased complexity of yours. But I don't have a tool to check the timing of these formulas when run on the worksheet. – Ron Rosenfeld Jun 7 '17 at 1:32
  • I doubt it would make a difference at all. I was just giving you a hard time. A little sarcasm. – Scott Craner Jun 7 '17 at 1:52
  • @ScottCraner I overlooked the emoji. Mine is kind of a "brute force" method. – Ron Rosenfeld Jun 7 '17 at 1:55
1

You can use the fact that there are 7 31sts per year to your advantage. For start date in B1 and end date in B2:

=(YEAR(B2)-YEAR(B1)+1)*7-INDEX({0,1,1,2,2,3,3,4,5,5,6,6},MONTH(B1))-INDEX({7,6,6,5,5,4,4,3,2,2,1,1},MONTH(B2))+(DAY(B2)=31)

Rationale:

  • 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.

Example:

Start: 9/6/2013
End: 12/31/2016

  • 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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.