0

How can I sort my report by date from a text field?

Leo Lee

August 01, 2019 16:19

None

Follow

Hello

I have a list of records that I would like to sort in ascending date order but the dates are entered into a free text field that is not formatted as date in the database.  I therefore potentially have variations such as:

dd/mm/yy
dd/mm/yyyy
d/m/yyyy
dd-mm-yy

Furthermore, even if all dates follow the same format (e.g. dd/mm/yyyy), the report seems to only sort by the number before the first "/".  So for example:

02/05/2019
05/01/2019
10/10/2019
23/09/2015
28/10/2016

Is what I am trying to do possible so that it is sorted correctly?

Many thanks in advance

• Facebook
• Twitter
• LinkedIn

1 comment

• 0
  
  Bryan Robinson September 06, 2019 14:25

  What is your ERP that you are reporting from? For a NAV data source you would do probably do something like:

  =NL("Rows","Cust. Ledger Entry","Customer No.","+=Datevalue(nf(,""<<Date Field>>""))","*")

  This should convert your date text to a date value using Excel's DATEVALUE function but I can't test this without a date as text field, which I don't have.