Re: converting a date?

In article <SYTBi.3575$xg.87@trnddc05>, Matt Wills <I'm@xxxxxxxx> wrote:

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.

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)
.