Microsoft Excel Formulae to Convert Date from DD/MM/YYYY to YYYYMMM


Just want to record this down on my Blog so that I can use this as again.

I encounter a scenario where I need to group item in such a way for my recent report that is using Microsoft Excel.

1. Group by Item Type

2. Group by Year and Month

3. Sum up the Qty of the Item

The Item type is nothing. The challenge is in the raw data excel sheet, the date is in format of DD/MM/YYYY but I need it to be YYYYMMM.

I tried to play with formulae such as =Year()&Month() and tried with date format… It did not turn out what I want.

With changing date format, I almost got it but when I use the raw data for pivot table, the original date format appears with the “days” and I did not managed to group the item by YYYYMMM format.

Screen shot below shows what I mean:

Using Custom Format to “YYYYMMM”


Yup.. It works. But….


Under Pivot table, It shows the actual date again. NOT what I want!



I know I can always copy and paste the value back but I want something that fast. So, I explore on formulae further.


So.. what Formulae I used in the end in Microsoft Excel?

=TEXT ( CELL, “yyyymmm”)

It WORKS!! Smile

Below screen shot shows the formulae I used to convert my cell containing the date.


With that, even under my pivot table, data is able to be grouped accordingly!


YES! With that, I can do my data analysis faster! Smile

This entry was posted in Everyday Life, Microsoft Office and tagged . Bookmark the permalink.

3 Responses to Microsoft Excel Formulae to Convert Date from DD/MM/YYYY to YYYYMMM

  1. deepa says:

    very useful.. thanks

  2. Harshad says:

    B9 is the cell which contains the date in its original format. Mite want to convert it to Long Date to avoid confusion.
    This will work on Office 2010

  3. S says:

    but it does not sort it in the pivot in date order ?

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s