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

Challenge

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”

image

Yup.. It works. But….

image

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

image

 

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

Solution

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.

 image

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

image

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

Advertisements
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:

    =DATE(YEAR(B9),DAY(B9),MONTH(B9))
    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:

WordPress.com Logo

You are commenting using your WordPress.com 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