=month formula no working

[PG]

I import text in to excel and in one cell it’s listed a such:
' 11-JAN-07

Now I have tried clearing the format and changing the cell to a date, but
the =month formula does not work. Do I did the following:

Cell A1 (' 11-JAN-07)
Cell B1 =A1
Cell C2 =month

Now I got that to work once and I have not been able to make it work again.
Help!!!

 

[Dave Peterson]

So each entry comes in with an apostrophe and a space followed by the date?

If the entries are all in a column
select that column
Data|Text to columns
Fixed width
draw a line between the space and the day
Skip that first field
and choose dmy for the date type

(reformat to the way you want if you need to)

And then try your =month() formula.

 

[daddylonglegs]

I imagine it'll be a good idea to convert your dates as Dave suggests, you
can then more easily do any calculation with them ......but to extract the
month from the data as is.....

=MONTH(RIGHT(A1,8))