Converting a string to a date in Excel

Question

I have a column with date values in the format dd mmm yyyy. For example, "22 Feb 2014". The month is a string. Is there any way I can convert it to an Excel date?

What I really want to do is count the dates that fall in individual months. That is count how many entries in January, how many in February, etc.

Is there any way I can do it without having to change the existing date value format?

Answer 1

if dates are strings you can use this formula:

=SUMPRODUCT(1*(MID(A1:A10;4;3)="Feb"))

In the example dates (strings) are in A1:A10 and you want to know how many Feb

or use

=COUNTIF(A1:A10;"*Feb*")

Answer 2

Use this formula:

=DATEVALUE(A1)

Then you can extract a month from this:

=MONTH(B1)

Then you can count entries for every month:

=COUNTIF($C:$C,2)
=COUNTIF($C:$C,3)
....

Answer 3

If you only want to count entries group by month and don't need to deal with the real date then just use another column for month

For example in column A there are the dates string in form "dd mmm yyyy". In column B you can use the formula

=MID(A1, 3, 3)

to extract the month, then use autofilter/sort to rearrange the tables according to column B and count. After that if you don't need column B you can hide it away

If you need the date value then use the solution here