November 20, 200718 yr Newbies hello, I have what I hope is an easy question. I have a file I am importing with a date formated: Nov 20, 2007, modifying the source is not an option unfortunately. Problem is, Filemaker is not seeing that as a date, so I am not able to do searches based on date. Is there a calculation field I can create that will format my source field into a date that filemaker will recognize? or is there a better way of doing this? thanks in advanced!
November 20, 200718 yr It's not pretty, but here: Substitute ( yourField ; [ "Jan" ; 1 ] ; [ "Feb" ; 2 ] ; [ "Mar" ; 3 ] ; [ "Apr" ; 4 ] ; [ "May" ; 5 ] ; [ "Jun" ; 6 ] ; [ "Jul" ; 7 ] ; [ "Aug" ; 8 ] ; [ "Sep" ; 9 ] ; [ "Oct" ; 10 ] ; [ "Nov" ; 11 ] ; [ "Dec" ; 12 ] ; [ " " ; "/" ] )
November 20, 200718 yr You will also want to remove the comma so add another line to your substitute calc... [ "," ; "" ]
November 20, 200718 yr How about this one. Date((Position("JanFebMarAprMayJunJulAugSepOctNovDec", LeftWords(Yourdate, 1), 1, 1) + 2) / 3, MiddleWords(Yourdate, 2, 1), RightWords(Yourdate, 1)) HTH Lee
Create an account or sign in to comment