Re: converting a date?

In article <oKJBi.22934$Zg.9143@trnddc08>, Matt Wills <I'm@xxxxxxxx>
wrote:
bobw@xxxxxxxxxxx wrote:

someone entered all dates in a text field in the format, "29-Sep-07"

How do I convert them all to standard FM date format, "09/29/2007"?

There are about 500 records covering several months' worth of dates.

This would do it, either as a separate calc field or as Replace Field
Contents:

Date =

Let (

[ Da = Left ( YourDateText ; Position ( YourDateText ; "-" ; 1 ; 1 ) - 1 ) ;
Mo = Middle ( YourDateText ; Position ( YourDateText ; "-" ; 1 ; 1 ) + 1
; 3 ) ;
Yr = Right ( YourDateText ; 2 ) ;
MoNum = Position ( " JanFebMarAprMayJunJulAugSepOctNovDec" ; Mo ; 1 ; 1
) / 3 ] ;

MoNum & "/" & Da & "/" & Yr )

Personally I'd convert it to a proper date rather than just another
reformatted Text version. That way you can use the in-built date format
options to display it however you want on different Layouts.

In Matt's custom function you only need to change the last line from:

MoNum & "/" & Da & "/" & Yr )

to this instead:

Date (MoNum, Da, 2000+Yr)

This function really needs a four-digit year, so I've assumed all your
years are from 2000 onwards.




Helpful Harry
Hopefully helping harassed humans happily handle handiwork hardships ;o)
.

Relevant Pages

  • Re: converting a date?
    ... Helpful Harry wrote: ... There are about 500 records covering several months' worth of dates. ... either as a separate calc field or as Replace Field Contents: ...
    (comp.databases.filemaker)
  • Re: converting a date?
    ... either as a separate calc field or as Replace Field Contents: ... There are about 500 records covering several months' worth of dates. ... Free FileMaker Technique Demos: http://www.VirtualVermont.com/FMP ... My Custom Functions: ...
    (comp.databases.filemaker)