Calculating the number of dates within two specific dates.

  • Thread starter Thread starter Chrissy
  • Start date Start date
C

Chrissy

I am using an array formula to calculate the number of
times certain pieces of text appear within a column range.
This is pretty straightforward, however, also within this
column range are a whole host of dates, lots with the year
2003, 2004 and 2005.
What I need to be able to do is count how many dates there
are between two specified dates - ie: if I had 23-Aug-03,
28-Sep-03, 30-Sep-03, 15-Oct-03 and 27-Dec-03 listed, I
want to be able to calculate that between 01-Aug-03 and 31-
Dec-03 there was a total count of 5.

How can this been written into the formula.

Any assistance would be appreciated.

Thanks
 
ARRAY FORMULA:
=SUM(IF((A1:A5>=VALUE("8/1/2003"))*(A1:A5<=VALUE
("12/31/2003")),1,0))

If you used cell references instead of the VALUE
function, this would obviously be shorter. For example
=SUM(IF((A1:A5>=B1)*(A1:A5<=B2),1,0))
where B1 contains 8/1/2003 and B2 contains 12/31/2003
 
Thank you so much Rick, I will certainly put that to the
test in work tomorrow.

I have only just begun to learn about using formulas and
yesterday was my first time at arrays (phew!) but I'm
getting there and with your help, I should hopefully
impress the boss!

Thanks again - you're a star!
 
The formula can be shortened since XL's implicit data conversion makes
the IF redundant.

Using cell references for the start and end dates (D1 and D2
respectively), use the array formula
=SUM((A1:A5>=D1)*(A1:A5<=D2))

Further, for reasons I don't understand, often the *non-array* use of
SUMPRODUCT duplicates the array-formula SUM. In this case,
=SUMPRODUCT((A1:A5>=D1)*(A1:A5<=D2))

If one wanted to avoid implicit conversions, the safe approach would be
the array-formula:
=SUM(IF(A1:A5>=D1,1,0)*IF(A1:A5<=D2,1,0))

A non-array formula that also avoids implicit conversions is
=COUNTIF(A1:A5,"<="&D2)-COUNTIF(A1:A5,"<="&D1)

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
 
Back
Top