Counting dates in MS Excel

Microsoft Excel 2007
January 2, 2010 at 02:25:46
Specs: Windows XP
Please help - I'm sure this has already been asked and answerred but I'm really struggling to find the answer I understand.

In Excel - I'm trying to get a column of dates to add up and tell me how many date = Jan, how many = Feb and so on. Example. Dates column is formatted like 01-Jan-10. If there are 15 entries of Jan then I want the system to report back in another cell 15, if Feb had 24 enteries then 24 reports back and so on throughout the year.

Thanks for any help! I'm sure I'm going to kick myself when I find out the answer - but thanks anyway!

See More: Counting dates in MS Excel

Report •

January 2, 2010 at 08:01:35
What kind of entries are you talking about?

Report •

January 2, 2010 at 09:13:51
There's probably some built-in Excel functions you can use, but I'll show you the VBA way:
1) Add/Pick a (new) VBA Module
2) In that module, paste the following code:
Function CountIfSucks(r As Range, m As Integer) As Long
  Dim c As Range
  For Each c In r
    CountIfSucks = CountIfSucks + (1 And (Month(c) = m))
  Next 'c
End Function

3) In the desired cell, type:
=CountIfSucks(<range>,<number of month>)

Jan = 1, Feb = 2 . . . Dec = 12

For the less VBA way, your best bet is the Office forum. I'll send this thread there.

Report •

January 2, 2010 at 10:45:10
Ok sorry maybe not quite a good description on start-up!

All that I have, on sheet1 a column where a date stamp is entered. eg. 01/01/2010.(next cell we type blar blar blar this happened... now there might be 10 lines happen in Jan but all with different dates. then Feb comes along and the same thing happens again
At the end of all this - I want a cell for each of the months to count how many accuracies happened in Jan, how many in Feb and so on.

Pauls / Razor thanks for your help(s)

Report •

Related Solutions

January 2, 2010 at 10:45:53

Here is one way to do this.

Put dates in column A. For this example I had dates in cells A2 to A24.
(You can change the location of the dates later, once this is working).

To get the 12 months as headers:
In Cell B1 enter 01-Jan-2010 and format in a suitable date format
In Cell B2 enter the formula =EOMONTH(B1,0) and format in a suitable date format
In Cell C1 enter the formula =B2+1
In Cell C2 enter the formula =EOMONTH(C1,0)
Select cells C1 and C2 and extend the formulas out to column M by dragging or copy and paste.
Cell M2 will now have 31-Dec-2010

Now the formula that does the real work:
In cell B3 enter this formula: =SUMPRODUCT(($A$2:$A$24>=B1)*($A$2:$A$24<=B2))
Note the $ signs
Extend this formula out to Column M

The number of dates falling in each month will now be in row 3, under each date range.


Report •

January 2, 2010 at 11:12:41

You can also do this with just one row for dates. If Cells B1 to M1 contain the dates for the first of each month, then replace the formula in B3 with this:
and extend the formula out to column M as before.
Format the dates in Row 1 with "mmmm" to just show the month.
(Just entering month names in row 1 won't work - you have to start with full dates for the first of each month.

PS If anyone using Excel 2003 tries this, please note that the EOMONTH() function is not installed by default. You have to have the Analysis ToolPack Add-in loaded).

Report •

January 2, 2010 at 11:37:37
Guys - Think we're trying to complicate all of this. Maybe its me and my usless decriptions.
Does this help?
"Cell C5"

Then on another sheet I have date heading across the top.
Jan Feb Mar April May etc...

This is what I need...based on the dates above, I need formula that will look through the dates and work out how many are in Jan, how many are Feb, on. Looks like this.

Jan | Feb | Mar | Apr | May |
7 | 4 | 2 | 2 | 3 |

Report •

January 2, 2010 at 12:35:27

If you use the formula in Response #5, as long as your list of Months across the top of the 'other sheet' are real Excel dates, it will work.

Instead of Jan Feb etc
enter 01-Jan-2010 01-Feb-2010
Then format them as "mmm" so that they still show as Jan Feb etc.

Under each month enter:
where B1 contains the month
Change $A$2:$A$24 to point to your column of dates on the first sheet e.g. Sheet1!$C$5:$C$50


Report •

January 2, 2010 at 12:49:00

With your dates in cells A2 to A19 on Sheet1

Here is my result on Sheet2:

	A	B	C	D	E
1	Jan	Feb	Mar	Apr	May
2	7	4	2	2	3

Cell A1 had the date 01-Jan-2010 entered and formatted as "mmm"
Cell A2 is:


Report •

January 2, 2010 at 13:52:38
All - I've tried all the options you've all giving to me and found Response Number 2 from Razor2.3 seems to work best. Although It gives me an issue with Dec's feedback and give me numbers like 170 for Dec even though there's no dates for Dec. Anyhow, its working for all the other dates.

Thanks for you time / Efforts for me. All the best for 2010


Report •

January 2, 2010 at 13:59:50
oops Sorry Humar didn't see your reply until after my last post. That's exactly what I'm after and looks easier to do!!

I'll give this option another looking at - This line EOMONTH - this worries me. If I do a lookup within my functions I don't see this as an option? I'm using office 2003.

Cheers buddy

Report •

January 2, 2010 at 14:35:41
By-George I think we have it!!!!

All working fine - Because I'm using Office 2003 the EOMONTH did start being used until 2007. However, there is an Add-on for it and now all works great.

This is a deffo close call now - Its all working and happy.

Really fella's, thanks for your(s) time


Report •

Ask Question