convert text with Month Name to date
I have data with the dates formatted as "September 25, 2008".
How can I get FileMaker to recognize this as a valid date?
Let's say this is in a field named DateText.
Let ( Month = Ceiling ( Position ( "JanFebMarAprMayJunJulAugSepOctNovDec" ; Left ( DateText ; 3 ) ; 1 ; 1 ) / 3 ) ; Date ( Month ; MiddleWords ( DateText ; 2 ; 1 ) ; Rightwords ( DateText ; 1 ) ) )
My solution was much more complicated, using a Case statement to set the text month to a numerical value.
Never seen that Month = Ceiling thing before, and I have never used a Let statement before. Gonna have to research how that works.
All the functions are documented in FileMaker help and Ceiling is one function I learned about here in the forum and have been using ever since whenever I need to round all fractional amounts up to the next larger whole number.
Though if the actual data was entered as a date (eg: 11/25/2008) you could still have formatted it on any layout to display as "September 25, 2008" and you wouldn't need the calculation. I assume that you are stuck with data that is actually the text of 'Septem.." etc.
That was my assumption, text either entered previously or imported from an external source...
correct, the data I am getting has the month spelled out.
Retrieving data ...