• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Count how many times a given day of month occurrs between two dates

E

ELanc

New Member
Hi,

I have a file containing several date ranges, where column A is the "start date" and column B is the "end date". I'm having trouble creating a formula in column C that will count how many time a specified day-of-month (DOM) (i.e. 15th) occurs between those two dates. The DOM can be a cell reference or hard coded into the formula. This is probably nothing complicated, but...

The the start and end dates can be any date and will not necessarily be on the DOM being counted. However, the start date should not be counted if it falls on the DOM, but the end date should be counted (e.g. counting the 15th in the date range 3/15/2013 thru 5/15/2013, should result in 2).

Thanks.
 
Assumptions:
A2 = start date
A3 = end date
A4 = DOM you are looking for.

=SUMPRODUCT((DAY(ROW(INDEX(A:A,A2+1):INDEX(A:A,A3)))=A4)*1)
 
Thanks. My data is a little arranged differently, but I could fit it to your concept above and got it to work. That was brilliant to create an array of every day through the two dates. I never would have thought of that. So in my file each row is a new scenario to count occurences between the listed dates.

Here is the adjustment to above:
Assumptions:
A2 = start date
B2 = end date
C2 = DOM

=SUMPRODUCT((DAY(ROW(INDEX(A:A,A2+1):INDEX(B:B,B2)))=C2)*1)
 
Glad you liked it. I think the 2 arrays in the INDEX functions should be the same though...otherwise you'll end up with a range the required number of days/rows tall, but 2 columns wide. :confused:
=SUMPRODUCT((DAY(ROW(INDEX(A:A,A2+1):INDEX(A:A,B2)))=C2)*1)
 
You must log in or register to reply here.
Back
Top