on 10-16-2015 1:57 PM
Hello experts,
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.
04062015
04202015
06032015
06242015
07082015
I did create several test variables to manipulate. All of which give # ERROR
This, works.:
variable_change date
=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?
Thanks
Chuck
Hi,
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")
Amit
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Thank you guys all the responses.
Its seems I had a field which was taking sort preference, if that is possible. When I tried to sort by my 'order date' column it did not work. When I removed this other string 'data' column. The sort by date worked fine. No variables needed. The solution by amit also works.
Thanks..
Chuck
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Chuck,
Covert the formatted variable from string to date using ToDate function and check.
Grtz
-Anila.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Chuck,
Shouldn't it sort automatically in the report?
I believe you just have to change the display format by applying a custom format, Right click on the Order Date & Time column, select Format Number, in the window that pops up, click on Date/Time and select a date format you want to use 9/21/2004 (sample value I see which is of M/dd/yyyy format).
If you dont want the "/", after selecting this format, click on Custom to bottom left and update the format to MMddyyyy, click Add and OK.
Thanks,
Mahboob Mohammed
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
84 | |
24 | |
11 | |
9 | |
7 | |
6 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.