• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Month Name Always Shows as January

O

ozziewendy

New Member
I am in Australia so have my dates formatted as dd/mm/yyyy. I wish to show the month name for a date, so I can set up a pivot table for the data. I use the =Month() formula to obtain the month number, then format the cell with mmmm to show the name, however no matter what my date the month always shows as January. I think it gets confused between American and Australian date formats, and converts the month number (say 8) to an American date 8/1/1900, but then the function looks in the correct place to convert it to a name, so always comes up with January.


ok, I've thought about it some more and instead of using =month() to get the month number in my cell I'm just letting the cell obtain the entire date, then the mmmm format is giving me the correct month name. That gets me around my problem, but still Excel should have worked the first way.
 
Hi Wendy


I am in Oz too!!! Month will only give you the month number and 7 is not compatible with say the Month of July. If you want to use Month what I usually do is use the following.


=TEXT(MONTH(A1),"mmm")


This will give you Jul if the date in A1 is 1/7/2013. If you wan the full month name add an extra "m" to the formula.


Take care


Smallman
 
Hi Wendy ,


There is some confusion !


Cell formatting can use a date value in a cell to display anything , ranging from a date to a month to a day to even a blank !


When you format a cell as mmmm it will display the month name , provided the cell contains a valid date ; if it contains a valid numeric value , Excel will interpret it as a date , which is why when you put in the formula =MONTH(...) , the result is a valid number from 1 through 12 ; Excel interprets it as a valid date , since dates are just numbers , starting from 1 which will be interpreted as a valid date of 1/1/1900 ; 12 will be interpreted as the valid date 12/1/1900 ; thus , for any value returned by the MONTH function , the result of the cell formatting will be January.


If you really want the cell to display the correct month names , just put the date in it , and format it as mmmm ; the date can be a manually entered date or a formula which returns a date value , not something that returns a value from 1 through 12.


Narayan
 
Thanks for the above posts. I am only working on this at my volunteering position half a day a week which is why the delay. I had thought of having the entire date in the cell rather than the month number, but the purpose of the month name is to appear in a pivot table, and when I use the full date the pivot table puts in a line for each different date, rather than each different month. I think I'm going to have to construct a horrendous IF statement and just interpret the date into the month name that I want. The other thing is that the pivot table is supposed to be in financial year rather than calendar year order (so starting with July), so all I can think of is manually converting the real date into 01July, 02August and so on.
 
Hi Wendy,


How about using the "Group" in pivot to do this.

http://chandoo.org/wp/2009/11/17/group-dates-in-pivot-tables/
 
ok, I've just reread Smallman's post, and I've used his technique except used =TEXT(A1),"mmmm") which seems to work for the pivot table. Thanks for that. Although I still need to figure out how to put it in FY order, but at least I am one step further forward, and my half day is almost over this week so I can sleep on it for another 7 days!


Sorry Sara, I didn't see your post, I'll investigate this.


Group! Fantastic! You can see I'm a bit of a pivot table novice, thanks for this. If I'd asked the right question in the first place I would have had more chance of getting the right answer.
 
I'm back again, having done nothing on this report for some time I am now trying to finish it off. My report is based on financial years, which in Australia run from 1 July to 30 June. When I group the dates, I find that a date for, say, May 2013 appears in the pivot table before a date for July 2012. How do a get the months to show in the correct order, taking the year into account?
 
Hi Wendy,
I normally add a column to my table for Financial year and use an IF statement to get the Year in that column
Code: 
=IF(MONTH([@Date])<=6,"FY"&YEAR([@Date]),"FY"&YEAR([@Date])+1)
 
OK, I see what you are saying. As the dates being grouped are already dd/mm/yyyy, I would have thought that they would show in the correct order in the pivot table. However, what you said made me think, so now I have grouped the dates by both month and year in the pivot table. Looks a little sloppy but works, I don't really care that the year displayed isn't strictly the FY, at least the months are in the right order.

Thanks.
 
another workaround for starting of the month is assume date is in cell a1
this formula will give first date of the month in cell a1
Code: 
EOMONTH($A$1,-1)+1
 
When calculating months within a formula:

cell A7 contains the date "06/03/2020"

Using a formula in cell H20... to display the number of the month, the formula would be:
  • =month(a7)
returning the value of the month, which is the number "6". If you then try to use the text formula:
  • =text(month(a7),"Mmmm")
to change this number into a date, Excel sees the value as a "1" and defaults to "January".
However, when you want a number of a month to be changed to a name, then you would use THIS formula:
  • =text(1*29,"Mmmm") or =text(a7*29,"Mmmm")
It is therefore an analytical jump to the following formula:
  • =text(month(a7)*29,"Mmmm")
which returns "June". Now, if you wanted to manipulate the formula to show a different month (previous or following),
you would simply add a "+" or a "-" after the month:
  • =(MONTH(A7))-1
NOTICE that you have to parenthesize "Month(A7)" first for Excel to understand that you want that value calculated with
the "1". To now tweak the formula to display the month, the text formula would evolve to:
  • =TEXT(((MONTH(A7))-1)*29,"Mmmm")
Parentheses and punctuation are how computations are recognized in a computer, and although frustrating at times, have to
be 100% correct for the logic to be understood.
 
sctlippert
As a new member, You have today read Forum Rules

Site Rules - New Users - Please Read

Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...
chandoo.org
Please, reread those and
You'll soon remember few basic things
eg Start a new post every time you ask a question, even if the theme is similar. The original author may continue asking questions whilst the post is in progress and of course if you are answering questions you may need to ask questions of the initial poster.
This thread is about seven year old ... and ... Month Name Always Shows as January
 
You must log in or register to reply here.
Back
Top