cancel
Showing results for 
Search instead for 
Did you mean: 

Sort report by date

former_member557046
Participant
0 Kudos

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

Accepted Solutions (1)

Accepted Solutions (1)

amitrathi239
Active Contributor
0 Kudos

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

Answers (3)

Answers (3)

former_member557046
Participant
0 Kudos

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

former_member211235
Active Participant
0 Kudos

Hi Chuck,

Covert the formatted variable from string to date using ToDate function and check.

Grtz

-Anila.

mhmohammed
Active Contributor
0 Kudos

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