# Formula to count number of times a date is 15 or 28 in a date range

1. ## Formula to count number of times a date is 15 or 28 in a date range

We're looking for a formula to count the number of times at date is either the 15th or the 28th between two dates in excel.

The goal of the field is to show the number of Semi Monthly Checks in a date range.

For example: from 1/1/2017 - 3/3/2017, there are 4 Semi-Monthly Checks.

Semi Monthly means they get paid twice a month. Usually the 15th and end of month.

I don't know much about VBA or Excel formulas.

Can anyone assist?

Thanks!  Register To Reply

2. ## Re: Need help with a formula that counts number of times a date is 15 or 28 in a date rang

You could do a text or OR(DAY(cell-with-date=15),DAY(cell-with-date=28)  Register To Reply

3. ## Re: Need help with a formula that counts number of times a date is 15 or 28 in a date rang

See if this does what you want.

Entered in C1 as in the below and copied down.
Formula:  `Please Login or Register  to view this content.`

 Row\Col A B C D 1 1/1/2017 3/3/2017 4 =SUMPRODUCT((DAY(ROW(INDIRECT(A1&":"&B1)))=15)+(ROW(INDIRECT(A1&":"&B1))=EOMONTH(ROW(INDIRECT(A1&":"&B1)),0))) 2 4/1/2017 7/14/2017 6 3 7/1/2017 7/13/2017 0  Register To Reply

4. ## Re: Need help with a formula that counts number of times a date is 15 or 28 in a date rang

Hello Dave,

That's an excellent approach!

As the last day of the month is obviously followed by the 1st of the next month you could also count the numbers of 15ths + end of months by counting whenever the next day is 16 or 1, e.g.

=SUMPRODUCT((DAY(ROW(INDIRECT(A1&":"&B1))+1)={16,1})+0)

....or if it's actualy 15th and 28th to count

=SUMPRODUCT((DAY(ROW(INDIRECT(A1&":"&B1)))={15,28})+0)  Register To Reply

5. ## Re: Formula to count number of times a date is 15 or 28 in a date range

I just slapped myself in astonishment!

The simplicity! I love it!

Thanks for the lesson and the back up.  Register To Reply

There are currently 1 users browsing this thread. (0 members and 1 guests) 