Determining number of specific days in period

**MJohnston** · 06-25-2009, 01:29 PM

Hi all~
I am wondering if there is a way in XL2003 to determine how many times specific days of the month occur within a specified date range.
For example, I need to find out how many times both the 1st of the month, and how many times the 15th of the month occur in the time period starting 05/02/2009, and ending 07/12/2009.
I was hoping to do this without macros, but any solution you can offer would be appreciated.

Thank you,

**NBVC** · 06-25-2009, 01:35 PM

If your start and end dates are in A1 and B1, respectively, try:

=SUMPRODUCT(--(DAY(ROW(INDIRECT(A1&":"&B1)))=1))

to get 1st's of the month counts

and

=SUMPRODUCT(--(DAY(ROW(INDIRECT(A1&":"&B1)))=15))

to get 15th's of the month counts

**MJohnston** · 06-25-2009, 01:51 PM

Nice!! That worked perfectly. Thank you so much for the response.
I am not very familiar with working with arrays, and don't quite understand why these formulae work. It looks like the elegant way to do what I would have spent time on a macro doing...that is to say, populate every day within that time period, and then count the days where the day() of the month is the first, and the fifteenth...is this what this is doing, or am I way off base here?

Either way, thank you again for the solution.

**NBVC** · 06-25-2009, 01:57 PM

That is exactly what it it is doing... as you might know, Excel treats dates as serial numbers.. so this formula takes the row numbers that equate to the serial numbers of your start date and end date and populates an array, triggered by the Sumproduct() function, with all the serial numbers (dates) between, then searches out the Day 1 and Day 15 in each of those serial numbers..

If you go to Tools|Formula Auditing|Evaluate Formula and keep clicking Evaluate, you will see how the formula comes up with that result, step by step.

Note that if you are using XL2003 or prior, your dates can't be later than sometime in, I think, year 2079, since the last Row() is row 65536 = June 5, 2079