=month formula no working
 

I import text in to excel and in one cell its 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!!!

=month formula no working
 

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.

PG wrote:

I import text in to excel and in one cell its 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

=month formula no working
 

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))

"Dave Peterson" wrote:

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.

PG wrote:

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

=month formula no working
 

Great, that worked!!

"Dave Peterson" wrote:

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.

PG wrote:

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

=month formula no working
 

Thanks, but it did not pick up all the cells. Some of them came back "value".
Daves answer work for me.


"daddylonglegs" wrote:

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))

"Dave Peterson" wrote:

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.

PG wrote:

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