Counting Dates

[reevsey23]

Hi. I have a series of dates within column E9:E3000. The dates are a record of when we have received a new business enquiry. I want to be able to count the number of new business enquires received each month to use within a graph. The count will need to look a the year also e.g number of enquiries in March 2011 & also March 2012.

Thanks Jon

 

[Robert Mika]

Could you post a sample of your data?

 

[Jonmo1]

Try

=SUMPRODUCT(--(TEXT(E9:E3000,"MMMMYYYY")="March2011"))

And instead of hard coding the monthyear into the formula, put any date within the month/year you want in a cell, Say A1

=SUMPRODUCT(--(TEXT(E9:E3000,"MMMMYYYY")=TEXT(A1,"MMMMYYYY")))


Hope that helps.

 

[T. Valko]

Hi. I have a series of dates within column E9:E3000. The dates are a record of when we have received a new business enquiry. I want to be able to count the number of new business enquires received each month to use within a graph. The count will need to look a the year also e.g number of enquiries in March 2011 & also March 2012.

Thanks Jon

One way...

Let's assume you want the count of dates for March 2011.

Enter the 1st of the month date in some cell:

• G9 = 3/1/2011

Then, enter this formula in H9 for the count:

=COUNTIF(E9:E3000,">="&G9)-COUNTIF(E9:E3000,">"&EOMONTH(G9,0))

Note that the EOMONTH function requires the Analysis ToolPak
add-in be installed if you're using a version of Excel prior to
Excel 2007. If you enter the formula and get a #NAME?
error look in Excel help for the EOMONTH function. It'll tell you
how to fix the problem.

 

[GaryWMn]

How about a PivotTable? Grouping the dates by month and year should give you what you need.

These are the overview steps
1. Create a PivotTable based on your table of data.
2. Add Date as a row field.
3. Also add Date (or another appropriate field) as the data field (with the count function).
4. Group your Date row field by years and months.

The result should look something like this.

<b>Excel 2003</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Count of Date</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="font-weight: bold;;">ID</td><td style="font-weight: bold;;">Date</td><td style="font-weight: bold;text-align: right;border-right: 1px solid black;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Years</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Date</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Total</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;">1</td><td style="text-align: right;;">2/21/1999</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1999</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;;">Feb</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;;">4</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;">2</td><td style="text-align: right;;">2/23/1999</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="border-right: 1px solid black;border-left: 1px solid black;;">Mar</td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;">19</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;;">3</td><td style="text-align: right;;">2/25/1999</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="border-right: 1px solid black;border-left: 1px solid black;;">Apr</td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;">16</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: right;;">4</td><td style="text-align: right;;">2/28/1999</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="border-right: 1px solid black;border-left: 1px solid black;;">May</td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;">16</td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: right;;">5</td><td style="text-align: right;;">3/1/1999</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="border-right: 1px solid black;border-left: 1px solid black;;">Jun</td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;">29</td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: right;;">6</td><td style="text-align: right;;">3/3/1999</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="border-right: 1px solid black;border-left: 1px solid black;;">Jul</td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;">20</td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: right;;">7</td><td style="text-align: right;;">3/6/1999</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="border-right: 1px solid black;border-left: 1px solid black;;">Aug</td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;">22</td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="text-align: right;;">8</td><td style="text-align: right;;">3/6/1999</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="border-right: 1px solid black;border-left: 1px solid black;;">Sep</td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;">16</td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style="text-align: right;;">9</td><td style="text-align: right;;">3/7/1999</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="border-right: 1px solid black;border-left: 1px solid black;;">Oct</td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;">22</td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style="text-align: right;;">10</td><td style="text-align: right;;">3/9/1999</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="border-right: 1px solid black;border-left: 1px solid black;;">Nov</td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;">20</td></tr><tr ><td style="color: #161120;text-align: center;">20</td><td style="text-align: right;;">11</td><td style="text-align: right;;">3/12/1999</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Dec</td><td style="text-align: right;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">16</td></tr><tr ><td style="color: #161120;text-align: center;">21</td><td style="text-align: right;;">12</td><td style="text-align: right;;">3/15/1999</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2000</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;;">Jan</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;;">24</td></tr><tr ><td style="color: #161120;text-align: center;">22</td><td style="text-align: right;;">13</td><td style="text-align: right;;">3/18/1999</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="border-right: 1px solid black;border-left: 1px solid black;;">Feb</td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;">15</td></tr><tr ><td style="color: #161120;text-align: center;">23</td><td style="text-align: right;;">14</td><td style="text-align: right;;">3/21/1999</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="border-right: 1px solid black;border-left: 1px solid black;;">Mar</td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;">15</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br />

You can learn more about this method in Mr. Excel's podcast episode 281.

Let us know if you need clarification on any of the steps.

Gary

 

[reevsey23]

Thanks to everyones replies. Now got the graphs up and running.
Regards Jon

 

[T. Valko]

Thanks to everyones replies. Now got the graphs up and running.
Regards Jon

Good deal. Thanks for the feedback!