MonthName in Excel 1

[andy98]

Hi

I have a dataset with a couple of thousand rows of data. One of the columns is a DATE.

I want to use this data in a Pivot Table, but I want to break the data down by Month e.g (Jan or Feb or Mar, etc)

How would I use the MonthName function as a formula to return the month?

Is this anywhere near..
=MonthName(E9, "mmm")

E9 being the cell that holds my date value.

Any help appreciated.

 

[GrayShades]

There are probably easier methods, but thsi works:

=CHOOSE(MONTH(E9),"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec")

Software: XL2002 on Win2K
Humanware: Older than dirt

 

[xlbo]

no need - if they are actual dates, just create the pivot table with date as row or column field, right click on the field, select Group and then choose "Months" from the options that pop up

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[DPlank]

I'd suggest making cell E10 (or other available cell) hold the formula '=E9'.

Then format the cell as Custom and type in mmmm in the box.

HTH
David

[joinedupwriting]
"whaddaya mean, 'invalid tag'?!?"
[/joinedupwriting]

 

[Zathras]

Geoff's method is best, but just to complete the picture, you were quite close with your first approach. You can get the month name with

 =TEXT(E9,"mmm")

To get the full month name (instead of the abbreviation) use "mmmm" as the format.

 

[andy98]

Zathras - using that formula just gives me a copy of the date that I already have.

I have (Sat 08/02/2003 16:27) in cell E9 and If I use =TEXT(E9,"mmm") as a formula on another cell I get Sat 08/02/2003 16:27.

I would just need "Feb" for that specific date.

 

[GrayShades]

Boy, did I have a brain freeze on that one! I knew I had done something like that many times, but - it isn't Monday, is it? LOL

Software: XL2002 on Win2K
Humanware: Older than dirt

 

[xlbo]

Andy - you need to format the cell as GENERAL rather than date for Zathras' solution to work. However, as I have already stated, there is no need for the formula, the pivottable can do the grouping for you

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[andy98]

Hi Geoff

I only started to look at Pivot Tables yesterday and don't see what you see when...

>>> right click on the field, select Group and then choose "Months" from the options that pop up
<<<

When I try, I don't get the Months option.

 

[xlbo]

Create the pivot table with your date field as a ROW or COLUMN field
Once the table has been created, right click on the DATE field and choose Group & Show Detail > Group

The grouping wizard will appear - choose "Months" and a start and end date (put the end date in the future) et voila, no need for any formula whatsoever

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[Zathras]

I have (Sat 08/02/2003 16:27) in cell E9

In that case, you don't have a DATE in E9, you have a STRING. So you could use either

=TEXT(DATEVALUE(MID(E9,5,10)),"mmm")

or simply

=LEFT(E9,3)

to extract the month abbreviation. If you don't convert your strings to dates, I don't think the pivot table approach will work for you either.

 

[andy98]

Top Man Zathras!

This works a treat.
=TEXT(DATEVALUE(MID(E9,5,10)),"mmm")

Many thanks

Could you explain the 5,10 part. I guess thats the part of the string that we want to extract. How does that correspond to.. Sat 08/02/2003 16:27

Many thanks once agai n

 

[Zathras]

You need to become familiar with the help file. (Just press F1.) The MID function takes three arguments:

MID(text,start_num,num_chars)

So, in this case, 5 means start with the fifth character (0) and take 10 characters (08/02/2003)