MonthNumber ( monthName )
Convert month name to a number
Average rating: 4.5 (45 votes) Log in to vote
|
David Head - Show more from this author
uLearnIT http://www.ulearnit.com.au/ |
Sample input:
MonthNumber ( "September" )
Sample output:
9
Function definition: (Copy & paste into FileMaker's Edit Custom Function window)
Returns the month number for any month name.
Comments
 |
Ward Clark, Dancing Bears May 15, 2010 |
|
| The function parameter name cannot be "monthName" because FileMaker 11 has a MonthName function. I changed the name to "text". | ||
 |
Thomas Keller, Portland Sep 3, 2010 |
|
| Very clever. Great use of custom functions. | ||
 |
Eric, SJSU Feb 17, 2012 |
|
| I wanted to be able to get the month number out of a complete date no matter where it was located (e.g. "January 1, 2012" or "08-FEB-12"), so I filtered: Position ( "xxJanFebMarAprMayJunJulAugSepOctNovDec" ; Left ( Filter (text; "JjAaNnFfEeBbMmYyUuLlGgSsPpOoCcTtVvDd" ); 3 ) ; 1 ; 1 ) /3 |
||
 |
David Feb 17, 2012 |
|
| Hi Eric Good feedback. The CF was designed to accept either a month name or an expression that results in a month name e.g. Monthname ( dateField ). So you have modified it to accept any input that may contain a month name. Fair enough use. This is a clever way to filter the required treat out of the string and then pass it to the function. Obviously the 'complete date' you are parsing is text not date. You have correctly used upper and lower case because the Filter function is case-sensitive. However, you have omitted "r" :) So I would tend to be more comprehensive and filter every possible letter to ensure completeness. I would also convert to uppercase (or lower) first to avoid the need for duplication: Filter ( Upper ( text ); "ABCDEFGHIJKLMNOPQRSTUVWXYZ" ) Anyway, a good addition to the functionality of the CF. :) Cheers, David |
||
 |
Brad Cassity Dec 17, 2018 |
|
| Thanks David! Good work. | ||
 |
Julio Toledo, Automation USA LLC Sep 22, 2019 |
|
| Hi David, Here's a further modification for your consideration. This takes any input and plucks out the first word of the first value and determines whether it's already a number or a string. If it's a number it validates it's in the range for the Gregorian calendar (1-12) If it's a string, it computes the month number for any of six Romance languages supported by FileMaker (where abbreviations of the first three characters of the month name will work). In all other cases, it returns "?" Cheers, --------------------------------------------------------- Let ( [ month_Name = LeftWords ( LeftValues ( month_Name ; 1 ) ; 1 ) ; lang = Get ( SystemLanguage ) ; abbr = Case ( PatternCount ( lang ; "English" ) ; "xxJanFebMarAprMayJunJulAugSepOctNovDec" ; lang = "Spanish" ; "xxEneFebMarAbrMayJunJulAgoSepOctNovDic" ; lang = "Italian" ; "xxGenFebMarAprMagGiuLulAgoSetOttNovDic" ; lang = "French"; "xxJanFevMarAbrMaiJunJulAgoSetOutNovDez" ; lang = "German" ; "xxJanFebMärAprKanJunJulAugSepOktNovDez" ; lang = "Portugese"; "xxJanFevMarAbrMaiJunJulAgoSetOutNovDez" ; //defaultResult (English) "xxJanFebMarAprMayJunJulAugSepOctNovDec" ) ; isNumber = If ( IsEmpty ( month_Name ); month_Name ; GetAsNumber ( Filter ( month_Name ; "01234567890" ) ) ) ] ; //calculation Case ( IsEmpty ( month_Name ) ; "?" ; Length ( month_Name ) < 3 ; "?" ; isNumber > 0 and isNumber < 13 ; isNumber ; IsEmpty ( isNumber ) ; Let ( result = Position ( abbr ; Left ( Filter ( Upper ( month_Name ); "ABCDEFGHIJKLMNOPQRSTUVWXYZ" ) ; 3 ) ; 1 ; 1 ) /3 ; //calculation |
||
|
Julio Toledo, Automation USA LLC Sep 22, 2019 |
|
| If ( result = "0" ; "?" ; result ) ) ; //defaultResult "?" ) ) |
||
Note: these functions are not guaranteed or supported by BrianDunning.com. Please contact the individual developer with any questions or problems.