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 )
Matt
bobw@rfphelp.com 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.
>
--
Free FileMaker Technique Demos: http://www.VirtualVermont.com/FMP
My Custom Functions:
http://www.briandunning.com/filemaker-custom-functions/resul ts.php?keyword=wills
Alternately,
Let ( [
Pos = Position ( YourDateText ; "-" ; 1 ; 1 ) ;
Da = Left ( YourDateText ; Pos - 1 ) ;
Mo = Middle ( YourDateText ; Pos + 1 ; 3 ) ;
Yr = Right ( YourDateText ; 2 ) ;
MoNum = Position ( " JanFebMarAprMayJunJulAugSepOctNovDec" ; Mo ; 1 ; 1
) / 3] ;
Date ( MoNum ; Da ; Yr ) )
That's what I love about FileMaker. There are so many ways to do something.
Matt
Matt Wills wrote:
> 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 )
>
> Matt
>
> bobw@rfphelp.com 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.
>>
>
--
Free FileMaker Technique Demos: http://www.VirtualVermont.com/FMP
My Custom Functions:
http://www.briandunning.com/filemaker-custom-functions/resul ts.php?keyword=wills
In article
wrote:
> bobw@rfphelp.com 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)
Helpful Harry wrote:
> In article
> wrote:
>> bobw@rfphelp.com 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
In article
> Helpful Harry wrote:
> > In article
> > wrote:
> >> bobw@rfphelp.com 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.
That's why I posted - I couldn't see anywhere that it was changing it
to a proper date. Your update message wasn't on my server then. :o)
> 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.
Two digit years are a pain in the sit-upon. You really have to know
what the dates are and carefully change them to four-digit years. It
can easily be possible to have dates in 2008 (using your "Yr > 07"
example), so it's always best to use four-digits from the start ... and
enforce that users do data entry that way too.
Helpful Harry
Hopefully helping harassed humans happily handle handiwork hardships ;o)