Re: converting a date?



Helpful Harry wrote:
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)


It's not just another reformatted text version, it enters characters as one would in a date field. With the calc returned as Date, a date is what it gives us.

I'll admit I zoned out on using the Date function at the end, but I changed that in a in a later message.

Yr is sufficient without adding 2000: FM assumes current century unless otherwise specified. That does suggest, however, that one might want to incorporate something that looks at Yr: in a past date field, If Yr > 07, then it's obviously not this century. That could also be extended to whether the date is after today.

Matt
.

Relevant Pages

  • Re: converting a date?
    ... There are about 500 records covering several months' worth of dates. ... either as a separate calc field or as Replace Field ... In Matt's custom function you only need to change the last line from: ...
    (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)