Excel Count Days Between Two Dates

Excel can be used to count the number of days between two dates.  Counting between dates is not all that intuitive as Excel does not have a ready made count dates formula. There are a number of ways we can attempt to count dates and the following example will talk through a couple of methods and how they can be extended to expand the formula.

To do this enter your dates in cell A12 to A16, start date in B12 and end date in C12, the following will count how many days fall within the two dates:

=SUMPRODUCT((A12:A19>=C12)*(A12:A19<=D12))

or for Excel 07 or later

=COUNTIFS(A12:A19,">="&C12,A12:A19,"<="&D12)

Excel count between dates

Let’ say the count needed to be extended to include multiple criteria. Now let’s say that we want to include another criteria which is not a date. The following formula will include a Yes/No indicator in the criteria. To extend the Excel formula to include another criterion as follows;

=SUMPRODUCT((A22:A31>=C22)*(A22:A31<=D22)*($B$22:$B$31=$E$22))

or

=COUNTIFS(A21:A31,">="&C12,A21:A31,"<="&D12,B21:B31,E22)

Excel count between many dates

To add more criteria simply add it in the same way.  For instance, If the next set of criteria was in Column C then add the Excel formula below to the end of the formula above. 


*($C$12:$C$19=$F$12)


or the complete Excel formula for 4 criteria.


=SUMPRODUCT((A12:A19>=C12)*(A12:A19<=D12)*($B$12:$B$19=$E$12)*($C$12:$C$19=$F$12))


To return a text value instead of a number value then the following article may be of assistance;  Match Multiple Text Criteria

The Excel file below shows both examples.