How to properly sort Mmm yyyy format date

I’ve had very often a requirement were I had to put a date in a format Mmm yyyy into a header of a crosstab. Creating just a calculation =[Month Name]+” “+[Year] will automatically sort an output in an alphabetical order.


Most of the time, if not always, the requirement will be to have a proper date order.

The first step would be to display a date in a crosstab header in a format MM/dd/yyyy. Make sure that you show only one day of the month in dd format, I choose first day of the month, this will guarantee that you will have only 1 month in each year displayed. In my case I use eFashion data so I need to create a formula like this:


If you have a full date you would need to truncate it to the first day of each month.

Now we have a proper sort of data, just the date format needs to be changed.


The second step we can do it in few ways:
Solution 1 – Apply a custom date format



Solution 2 – Apply an alerter with a proper date format. In an alerter condition you can put any condition that will always be true, I put =1=1. In the Display box put a proper format:

=FormatDate( ToDate([Month]+”/01/”+[Year];”MM/dd/yyyy”); “Mmmm yyyy”)



Solution 3 – Create an additional header row below and enter a date as follows:

=[Month Name]+” “+[Year]

The second header row would have a proper format:


Now we just need to make the first row white and decrease its height. This way the first row will enforce the right sort.


Personally I always use the first option because it’s easy and quick.


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 )

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s