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.

pic3.1

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:

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

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.

pic3.2

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

pic3.3pic3.4

 

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”)

pic3.5

 

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:

pic3.6

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

pic3.7

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

Advertisements

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