Convert dd/mm/yyyy DATE >> to mm/yyyy TEXT

[Mr.X]

I want to use parse data in a piviot table by months. The data is in the format of dd/mm/yyyy. Changing the date format to mm-yy does not change the date serial number and the pivot table still sees the individual days. I need to either convert the date code to mm/yyyyy or change the format from date to text. I'd settle for either solution.

Thanks

[FDibbins]

Hi and welcome to the forum

As you have seen, all formatting does to a cell is change the appearance of the data, it does not change that actual contents of the cell. If you want to extract the month number from a date, then use =MONTH(cell-ref). This will give you a numeric value from 1-12

You can also use =year() to extract the year, then combine =year(cell-ref)&month(cell-ref)
Note, this will give you a text answer, to convert that to a numerics...
=(YEAR(cell-ref)&MONTH(cell-ref)*1

[Mr.X]

That worked with a CONCANTINATE(MONTH(A2),"/",YEAR(A2))

I ended up using this, You are able to make your own - so named "custom" formats.
Right click follow Fomat cells... then Category: custom write to box Type: mm.yyyy and confirm ENTER.
You can use also Type: mmmm "-" yyyy

Or use something like this: =TEXT(A1;"mmmm.yyyy") suppose in cell A1 = 14.1.2014 => result is january.2014
Or =TEXT(A1;"mmmm - yyyy")

Thanks for the help.

Mr.X

[FDibbins]

great job

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, as a relatively new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

[Mr.X]

Problem [SOLVED]

is there a button I need to push?

[benishiryo]

hi Mr X. if you're using Pivot, you should make full use of its features of dates inside it. use the group function in Pivot table so that you don't have to change the source. right-click any of the cells with dates & click on Group. use Month & Year. This method's "downside" is that you won't be able to format to mmm.yyyy
but notice that my dates in the file are arranged in order because they are recognized as dates. they go from years, then from Jan to Dec. making them text will not work in this way.

few things required for it to work
1. your data source for the pivot cannot have blank rows. mine is A1:B20. so i cannot range A1:B100 where A21:B100 are blanks
2. dates in column A cannot have blanks. so even if i range A1:B20, A7 for eg cannot be blank
3. the cells for the date must be formatted as dates

Problem [SOLVED]

is there a button I need to push?

Ford answered this question in post # 4.