Livio/ April 24, 2018/ Excel, Excel Formulas/ 0 comments

The Data

A user on MrExcel Forum asked, given two columns one with a start date and one with an end date, for an excel formula to count the number of days which fall within a given month with just one formula, without helper columns. This will be a quick showing the formula I used in the post.

The data resembles something like the below:

Excel Formula to Count the Number of Days

Excel Formula to Count the Number of Days

The Formula

The formula that I will use allows for the user to specify a start date and an end date which represents the relevant period whose days need to be counted, which in my example happens to coincide with the full month of April.

Excel Formula to Count the Number of Days

Excel Formula to Count the Number of Days

The formula used is:

=SUMPRODUCT(SUBTOTAL(5,OFFSET(B2,ROW(A2:A6)-ROW(A2),0),G3)-SUBTOTAL(4,OFFSET(A2,ROW(A2:A6)-ROW(A2),0),G2)+1,(A2:A6<=G3)*(B2:B6>=G2))

The part: SUBTOTAL(5,OFFSET(B2,ROW(A2:A6)-ROW(A2),0),G3) retrieves , for each row of column B, the minimum between cell G3 (30 April) and the value in column B (end Date).

The part: SUBTOTAL(4,OFFSET(A2,ROW(A2:A6)-ROW(A2),0),G2) retrieves , for each row of column A, the minimum between cell G2 (1st April) and the value in column A(start Date).

The part SUBTOTAL(5,OFFSET(B2,ROW(A2:A6)-ROW(A2),0),G3)-SUBTOTAL(4,OFFSET(A2,ROW(A2:A6)-ROW(A2),0),G2)+1  will perform the difference for each value returned by the above functions and add 1

Now we are almost there, we just need to sum all the results of the above difference, the problem is that we need to exclude the periods whose start date is after April 30th or whose end date is before April 1st. This is what the second argument of the SUMPRODUCT is doing: (A2:A6<=G3)*(B2:B6>=G2): it will return an array of 0’s and 1’s (0’s will correspond to those rows whose start date is after April 30th or whose end date is before April 1st). 

 

Expanding the formula

Thanks to this set up now we can easily count the number of days that fall within different months, by using the below set up:

Excel Formula to Count the Number of Days

Excel Formula to Count the Number of Days

In D3 and drag down:

=SUMPRODUCT(SUBTOTAL(5,OFFSET($B$2,ROW($A$2:$A$6)-ROW($A$2),0),INDEX(H$3:J$3,ROWS(D$3:D3)))-SUBTOTAL(4,OFFSET($A$2,ROW($A$2:$A$6)-ROW($A$2),0),INDEX(H$2:J$2,ROWS(D$3:D3)))+1,($A$2:$A$6<=INDEX(H$3:J$3,ROWS(D$3:D3)))*($B$2:$B$6>=INDEX(H$2:J$2,ROWS(D$3:D3))))

 

How would you have counted the number of days?

me

Leave a Comment

Your email address will not be published. Required fields are marked *

*
*