Tech Support Guy banner
Status
Not open for further replies.
1 - 6 of 6 Posts

·
Super Moderator
Joined
·
65,526 Posts
Discussion Starter · #1 ·
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
 

·
Registered
Joined
·
2,556 Posts
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
 

·
Super Moderator
Joined
·
65,526 Posts
Discussion Starter · #3 ·
Thanks

How would that display in the drop down list - I assume alpha rather than date order ?
 

·
Super Moderator
Joined
·
65,526 Posts
Discussion Starter · #5 ·
sorry Ziggy1 - not sure how i would go about that
 

·
Registered
Joined
·
2,556 Posts
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).
 
1 - 6 of 6 Posts
Status
Not open for further replies.
Top