Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
PG PG is offline
external usenet poster
 
Posts: 11
Default =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!!!
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 35,218
Default =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
  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 287
Default =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

  #4   Report Post  
Posted to microsoft.public.excel.misc
PG PG is offline
external usenet poster
 
Posts: 11
Default =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

  #5   Report Post  
Posted to microsoft.public.excel.misc
PG PG is offline
external usenet poster
 
Posts: 11
Default =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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Find value in array Brook6 Excel Worksheet Functions 26 January 30th 07 10:40 PM
Is it possible? DakotaNJ Excel Worksheet Functions 25 September 18th 06 09:30 PM
Reusing formula Tony29 Excel Discussion (Misc queries) 7 September 7th 06 03:34 AM
Working Hours (formula & graph) - any elegant solution? markx Excel Worksheet Functions 1 March 29th 06 02:02 PM
Array Formula Not Working with Range with Formulas [email protected] Excel Discussion (Misc queries) 4 February 1st 06 03:01 PM


All times are GMT +1. The time now is 11:23 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"