Sort by Date in Excel when imported from Reporting Services

Question

I am trying to make a report so that when its exported to excel it can be sorted by date like so:

In my SQL the date is converted to a VARCHAR so it can be displayed in the format I want:

CONVERT(VARCHAR, a.a_start, 106)

I know that because it's a VARCHAR, when exported, excel will only sort A - Z. I've tried changing it to a date format in both SSRS and the query but this displays a DATETIME format in reporting services and is not what I was looking for.

So basically is there a way to display the date as the format 'dd/mm/yyyy' or 'dd month yyyy' in reporting services but have it sort-able by date when exported to excel?

Answer 1

There is a option on the tablix to format the cell, this is where you can set the "dd/mm/yyyy".

1. Right click on the cell / textbox and select properties.
2. Once the cell is a date format you will be able to sort in excel.