Sorting dates in a text box

[M D Shaw]

I have a Text field where I can record activity- calls to clients, projects etc. Each activity starts with the date (as 12/10/05 but in the text box). I now need to sort the records by the date of the activity entered.

Of course, when I do a sort at the moment, it does it by the first digits- which is completely wrong for the months & years.

Is there any way to force Sort to recognise the first 6 characters as a date & correctly Sort?

[M D Shaw]

I have a Text field where I can record activity- calls to clients, projects etc. Each activity starts with the date (as 12/10/05 but in the text box). I now need to sort the records by the date of the activity entered.

Of course, when I do a sort at the moment, it does it by the first digits- which is completely wrong for the months & years.

Is there any way to force Sort to recognise the first 6 characters as a date & correctly Sort?

[M D Shaw]

I have a Text field where I can record activity- calls to clients, projects etc. Each activity starts with the date (as 12/10/05 but in the text box). I now need to sort the records by the date of the activity entered.

Of course, when I do a sort at the moment, it does it by the first digits- which is completely wrong for the months & years.

Is there any way to force Sort to recognise the first 6 characters as a date & correctly Sort?

[Lee Smith]

Would your field look like this, one entry per text field

12/10/05 text text text

Or More than one entry per text field

12/10/04 text text text

12/12/04 text text text

01/15/05 text text text

Next, is the date consistent as 12/10/04 or more like this:

1/1/05

01/01/05

1/01/05

01/1/05

01/01/2005

Lee

[Lee Smith]

Would your field look like this, one entry per text field

12/10/05 text text text

Or More than one entry per text field

12/10/04 text text text

12/12/04 text text text

01/15/05 text text text

Next, is the date consistent as 12/10/04 or more like this:

1/1/05

01/01/05

1/01/05

01/1/05

01/01/2005

Lee

[Lee Smith]

Would your field look like this, one entry per text field

12/10/05 text text text

Or More than one entry per text field

12/10/04 text text text

12/12/04 text text text

01/15/05 text text text

Next, is the date consistent as 12/10/04 or more like this:

1/1/05

01/01/05

1/01/05

01/1/05

01/01/2005

Lee

[M D Shaw]

Hi Lee,

text is like this:

12/10/04 text text text

12/12/04 text text text

01/15/05 text text text

but I only need to view the first line- so I can see most recent activities when looked at as a list.

Dates are consistent as 6 digits: 01/01/05

Many thanks for your help- this problem has been irking me all day!

[M D Shaw]

Hi Lee,

text is like this:

12/10/04 text text text

12/12/04 text text text

01/15/05 text text text

but I only need to view the first line- so I can see most recent activities when looked at as a list.

Dates are consistent as 6 digits: 01/01/05

Many thanks for your help- this problem has been irking me all day!

[M D Shaw]

Hi Lee,

text is like this:

12/10/04 text text text

12/12/04 text text text

01/15/05 text text text

but I only need to view the first line- so I can see most recent activities when looked at as a list.

Dates are consistent as 6 digits: 01/01/05

Many thanks for your help- this problem has been irking me all day!

[comment]

The text field sorts correctly - after all, it IS a text field, and FMP cannot be expected to guess the meaning of the text.

You can add a calculation field (result date) =

Date (

Left ( text ; 2 ) ;

Middle ( text ; 4 ; 2 ) ;

"20" & Middle ( text ; 7 ; 2 )

)

and sort on that.

Note that this assumes the year is in the present century; if you have entries like 01/01/99, it needs to be enhanced a bit.

[comment]

The text field sorts correctly - after all, it IS a text field, and FMP cannot be expected to guess the meaning of the text.

You can add a calculation field (result date) =

Date (

Left ( text ; 2 ) ;

Middle ( text ; 4 ; 2 ) ;

"20" & Middle ( text ; 7 ; 2 )

)

and sort on that.

Note that this assumes the year is in the present century; if you have entries like 01/01/99, it needs to be enhanced a bit.

[comment]

The text field sorts correctly - after all, it IS a text field, and FMP cannot be expected to guess the meaning of the text.

You can add a calculation field (result date) =

Date (

Left ( text ; 2 ) ;

Middle ( text ; 4 ; 2 ) ;

"20" & Middle ( text ; 7 ; 2 )

)

and sort on that.

Note that this assumes the year is in the present century; if you have entries like 01/01/99, it needs to be enhanced a bit.

[Lee Smith]

You should create a calculation with the result = to date field and parse out the date.

You can start with this, and then one of the experts (hello Queue) will come along and clean it up or give you a better one.

TextToDate(Left(Text, 2) &"/" & Middle(Text, 4, 2 ) &"/" &"20" & Middle(Text, 7, 2))

HTH

Lee

Or, while I was out testing mine, along came comment with one, a tad different. I agree with the assumption that you will not have 1999 or earlier as a current contact date.

:P

[Lee Smith]

You should create a calculation with the result = to date field and parse out the date.

You can start with this, and then one of the experts (hello Queue) will come along and clean it up or give you a better one.

TextToDate(Left(Text, 2) &"/" & Middle(Text, 4, 2 ) &"/" &"20" & Middle(Text, 7, 2))

HTH

Lee

Or, while I was out testing mine, along came comment with one, a tad different. I agree with the assumption that you will not have 1999 or earlier as a current contact date.

:

[Lee Smith]

You should create a calculation with the result = to date field and parse out the date.

You can start with this, and then one of the experts (hello Queue) will come along and clean it up or give you a better one.

TextToDate(Left(Text, 2) &"/" & Middle(Text, 4, 2 ) &"/" &"20" & Middle(Text, 7, 2))

HTH

Lee

Or, while I was out testing mine, along came comment with one, a tad different. I agree with the assumption that you will not have 1999 or earlier as a current contact date.

:

[comment]

Sorry about that.

I don't particularly like relying on the "/" separators - move it to another system, and it breaks. However, if you want to approach it that way, then you can make it simply:

GetAsDate ( Left ( Text ; 8 ) )

Might be actually better in this case, since here FMP will automatically assign "19" to all years above 35.

[comment]

Sorry about that.

I don't particularly like relying on the "/" separators - move it to another system, and it breaks. However, if you want to approach it that way, then you can make it simply:

GetAsDate ( Left ( Text ; 8 ) )

Might be actually better in this case, since here FMP will automatically assign "19" to all years above 35.

[comment]

Sorry about that.

I don't particularly like relying on the "/" separators - move it to another system, and it breaks. However, if you want to approach it that way, then you can make it simply:

GetAsDate ( Left ( Text ; 8 ) )

Might be actually better in this case, since here FMP will automatically assign "19" to all years above 35.

[M D Shaw]

Thanks!!

GetAsDate ( Left ( Text ; 8 ) ) very elegantly fixed the problem & everyhting now Sorts exactly as I wanted it.

Again, many thanks to you all.

[M D Shaw]

Thanks!!

GetAsDate ( Left ( Text ; 8 ) ) very elegantly fixed the problem & everyhting now Sorts exactly as I wanted it.

Again, many thanks to you all.