Date Detect

[rivet]

My clients has contracts entered with loose descriptions for dates. Fortunately they are consistent with typing the month before the date.

e.g.

Fri June 29 - Sun July 1, 2007

Apr 26 - Apr 29, 2007

Friday Jan. 12th & Saturday Jan. 13th, 2007

For sorting reasons I would like a calc or custom calc what would extract the first date in the string.

Has anyone see such a function?

If not here is what I am thinking:

1. sub out all day name and abbreviations.

2. sub out all punctuation and ampersands.

3. sub out all double spaces.

4. sub out all number extensions 4"th", 3"rd".

5. sub all full month names to abbreviated 3 character names.

6. parse out abbrv + date: left (3+4)

7. convert to date.

7.

dates_detect.zip

[comment]

I would try it this way:

Let ( [

mask = Substitute ( Lower ( text ) ; 

[ "jan" ; "mmm" ] ; [ "feb" ; "mmm" ] ; [ "mar" ; "mmm" ] ; [ "apr" ; "mmm" ] ; 

[ "may" ; "mmm" ] ; [ "jun" ; "mmm" ] ; [ "jul" ; "mmm" ] ; [ "aug" ; "mmm" ] ; 

[ "sep" ; "mmm" ] ; [ "oct" ; "mmm" ] ; [ "nov" ; "mmm" ] ; [ "dec" ; "mmm" ] ) ;

posM = Position ( mask ; "mmm" ; 1 ; 1 ) ;

mmm = Middle ( text ; posM ; 3 ) ;

m = Ceiling ( Position ( "janfebmaraprmayjunjulaugsepoctnovdec" ; mmm ; 1 ; 1 ) / 3 ) ;



num = Filter ( text  ; "0123456789" ) ;

posD = Position ( text ; Left ( num ; 1 ) ; 1 ; 1  ) ;

d = GetAsNumber ( Middle ( text ; posD ; 2 ) ) ;



y = Right (num ; 4 )

] ;

Date ( m ; d ; y )

)

[rivet]

brilliant "comment" - thanks