Excel: Dates: Month

Status
This thread has been Locked and is not open to further replies. The original thread starter may use the Report button to request it be reopened but anyone else with a similar issue should start a New Thread. Watch our Welcome Guide to learn how to use this site.

etaf

Wayne
Thread Starter
Moderator
Joined
Oct 2, 2003
Messages
65,501
I have a strange problem I hope someone can shed some light on

I want to make a new column of months from a date
I have a detached with column A as a normal date format 01/12/2006 DD/MMM/YYYY
I then in column B use the month function - so = month(A1)
it displays 12 - and if I go onto the Fx to see the expression helper / wizard
I get told that i get a number returned 1-12 here 1 = Jan and 12 =dec

so that i format the =month(a1) into a MMM-YY format and
get Jan-1900 - I'm cool with the 1900 as i have not specified YYYY but why do i get Jan returned instead of Dec

But what I'm after and maybe a simpler way is to have a dropdown on my pivot table of MM-YYYY from my date field rather than DD-MMM-YYYY

so I can choose and show month data

I'm sure I did this in the past just by using format - MMM-YYYY on a date field and it worked in the picot table OK

any help appreciated
 
Joined
Jun 17, 2002
Messages
2,556
If you want to display the text format of the month use this expression, where A2 is your field with the full date.

=TEXT(A2,"MMM") Abreviated month

=TEXT(A2,"MMMM") Full month text

when you use the Month expression you need to change the format of the cell to a number, as this function only returns the number value of the month.

FYI you can also exchange the M's with D's to get the day's in text
 

etaf

Wayne
Thread Starter
Moderator
Joined
Oct 2, 2003
Messages
65,501
Thanks

How would that display in the drop down list - I assume alpha rather than date order ?
 
Joined
Jun 17, 2002
Messages
2,556
use the numeric as your sort and include the text as the identifier, you could create a custom sort with the text.
 

etaf

Wayne
Thread Starter
Moderator
Joined
Oct 2, 2003
Messages
65,501
sorry Ziggy1 - not sure how i would go about that
 
Joined
Jun 17, 2002
Messages
2,556
sort using the numeric month, and then use your text in the Pivot, you can also use custom click the OPtions button in the sort menu and you can choose the text Months to sort by that column (highlight the whole range or you will mix the data).
 
Status
This thread has been Locked and is not open to further replies. The original thread starter may use the Report button to request it be reopened but anyone else with a similar issue should start a New Thread. Watch our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Top