Sort report by date
I have the following column in a report:
[Order date & time]
04/20/2015 10:44:03 AM
06/03/2015 05:14:03 PM
06/24/2015 04:22:10 PM
07/08/2015 09:03:08 AM
04/06/2015 06:00:39 PM
I need to display the report chronologically (day/month/yr only. time is irrelevant) using this column. The ascending/descending /custom sort options do not work.
i want to sort by day/month/yr e.g.
I did create several test variables to manipulate. All of which give # ERROR
=Substr(FormatDate([Order Date & Time];"MM/dd/yyyy") ;1 ;10)
so 04/20/2015 10:44:03 AM becomes 04/20/2015. But cant sort on this field either.
Can anyone help?
Amit Kumar replied
Right click on the object->Formatnumber->Date/Time->Custom sort and define the format like dd/MM/yyyy.
After this apply the sort.
or with your formula use this to diaply data in dd/MM/yyyy format.
=Todate(Substr(FormatDate([Order Date & Time];"MM/dd/yyyy") ;1 ;10);"dd/MM/yyyy")