Skip to Content

Sort report by date

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

4 Answers

  • Best Answer
    Oct 16, 2015 at 01:19 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • Oct 16, 2015 at 01:28 PM

    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


    Snap 05.png (56.5 kB)
    Add comment
    10|10000 characters needed characters exceeded

  • Oct 16, 2015 at 01:37 PM

    Hi Chuck,

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

    Grtz

    -Anila.

    Add comment
    10|10000 characters needed characters exceeded

  • Oct 19, 2015 at 03:54 PM

    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

    Add comment
    10|10000 characters needed characters exceeded