First, change the type of your "date" field to Text. Then add a calculation field (result is Date) =
Ceiling ( Position ( "janfebmaraprmayjunjulaugsepoctnovdec" ; Middlewords ( ImportedDate ; 2 ; 1 ) ; 1 ; 1 ) / 3 ) ;
LeftWords ( ImportedDate ; 1 ) ;
RightWords ( ImportedDate ; 1 )
Check the results: if everything looks fine, you can change the calculation field into a Date field.
Wow! Thanks. Having help like this really makes me feel confident I can do this.
I do need a bit of clarification though. The Cell is called "date_of_service_p." I changed it to be a text cell instead of a date cell. When you say add a calculation field, are you saying change or add the option for that cell to now be a calculation and then add that calculation there? If so, FM has trouble with it if I just cut and paste it. It asks for a cell name etc. Or are you saying make a new field that calculates a result based upon that script?
Or are you saying make a new field that calculates a result based upon that script?
Yes, create a new field of type Calculation (with the result type being Date). In the formula above, change "ImportedDate" to the name of your field.
Thank you. Unfortunately the way I am doing it doesn't work. The results are identical to the previous cell. (I could send a picture clip of the script as I have it in the calculation) (2-Jun-2009 returns 2-Jun-2009)
What happens when you click into the calculation field (when you are in Browse mode) - does it stay "2-Jun-2009" or does it change to something else?
It stays exactly the same, still in the form "2-Jun-2009"
That's not possible: you must be looking at the original field, not the new calculation field.
Sorry, it appears I forgot to change the attribution to the newly created cell. Now it is just blank.
Got it! Thanks.
(Where I was going wrong was that I had to make sure the calculated cell was made a date and also that the calculation was a date, not a number. That last one is easy to overlook there at the bottom.)
I do appreciate your extra efforts.