Spell out the date (including year) container field

[babaloo21]

We are using FMP 12 Advanced, we have a layout in which we print an award certificate. We would like to be able to create a calculation field that draws from an existing date field (call the existing date field: "Date Awarded") the contents of that field would be a date value ex: 2/2/2012 this date may be a past, current, or future date, and will vary depending upon the actual date awarded. We would like to be able to add a calculation field (based on the "Date Awarded" field) which pulls the numerical representation of the example date "2/2/2012" and have it represented as a spelled out word: i.e. "This second day of December, two thousand and twelve". Further inputing "<<spelled out date>>" into our layout.

Thus far using a case function (found on briandunning.com) we are able to accomplish this for the date and month, but not for the worded year, here is what we have so far:

Case(
Day( Date Awarded ) = 1;"First of " & MonthName( Date Awarded ) & " " & Year( Date Awarded );
Day( Date Awarded ) = 2;"Second of " & MonthName( Date Awarded ) & " " &Year( Date Awarded );
Day( Date Awarded ) = 3;"Third of " & MonthName( Date Awarded ) & " " &Year( Date Awarded );


//….ETC. through the 31st of the month 

Day( Date Awarded ) = 30;"Thirtieth of " & MonthName( Date Awarded ) & " " &Year( Date Awarded );
Day( Date Awarded ) = 31;"Thirty-first of " & MonthName( Date Awarded ) & " " &Year( Date Awarded )
)

The problem is we cant figure out how to get the year to represent as a worded value i.e.: two thousand and twelve, or nineteen hundred and ninety nine, or two thousand and thirteen...

 

Any help would be much appreciated. :-)

[comment]

Try something like =

 

Let ( [
ordinals = "First¶Second¶Third¶Fourth¶Fifth¶Sixth¶Seventh¶Eighth¶Ninth¶Tenth¶Eleventh¶Twelfth¶Thirteenth¶Fourteenth¶Fifteenth¶Sixteenth¶Seventeenth¶Eighteenth¶Nineteenth¶Twentieth¶Twenty-First¶Twenty-Second¶Twenty-Third¶Twenty-Fourth¶Twenty-Fifth¶Twenty-Sixth¶Twenty-Seventh¶Twenty-Eighth¶Twenty-Ninth¶Thirtieth¶Thirty-First" ;
tens = "¶Twenty¶Thirty¶Forty¶Fifty¶Sixty¶Seventy¶Eighty¶Ninety" ;
units = "One¶Two¶Three¶Four¶Five¶Six¶Seven¶Eight¶Nine¶Ten¶Eleven¶Twelve¶Thirteen¶Fourteen¶Fifteen¶Sixteen¶Seventeen¶Eighteen¶Nineteen" ;
y = Year ( DateAwarded ) ;
C = Div ( y ; 100 ) ;
DU = Mod ( y ; 100 ) ;
D = Div ( DU ; 10 ) ;
U = Mod ( DU; 10 )
] ;
GetValue ( ordinals ; Day ( DateAwarded ) ) & " day of " & MonthName ( DateAwarded ) & ", " &
Case ( C = 19 ; "Nineteen Hundred" ; C = 20 ; "Two Thousand" ) &
Case ( DU ;  " and " ) &
Case ( D = 1 ; GetValue ( units ; DU ) ; GetValue ( tens ; D ) & Case ( D and U ;  "-" ) & GetValue ( units ; U ) )
)

I haven't really tested it and it needs more work to accommodate years before 1900 and after 2099.

[doughemi]

Try

Let(

[century=Case(Left(In Numbers;2) = "19"; "Nineteen "; Left(In Numbers;2) = "20"; "Two Thousand ");

decade = Middle(In Numbers; 3; 1);

year = Right(In Numbers; 1);

teens = Case(

     decade = 1 and year = 0; "Ten";

     decade = 1 and year = 1; "Eleven";

     decade = 1 and year = 2; "Twelve";

     decade = 1 and year = 3; "Thirteen";

     decade = 1 and year = 4; "Fourteen";

     decade = 1 and year = 5; "Fifteen";

     decade = 1 and year = 6; "Sixteen";

     decade = 1 and year = 7; "Seventeen";

     decade = 1 and year = 8; "Eighteen";

     decade = 1 and year = 9; "Nineteen";

     ""

     );

decadeword= Case(

     decade= 0; "";

     decade= 1; "";

     decade= 2; "Twenty";

     decade= 3; "Thirty";

     decade= 4; "Forty";

     decade= 5; "Fifty";

     decade= 6; "Sixty";

     decade= 7; "Seventy";

     decade= 8; "Eighty";

     decade= 9; "Ninety";

     );

 

yearword= Case(

     year= 0; "";

     year= 1; "One";

     year= 2; "Two";

     year= 3; "Three";

     year= 4; "Four";

     year= 5; "Five";

     year= 6; "Six";

     year= 7; "Seven";

     year= 8; "Eight";

     year= 9; "Nine";

     )

];

century   & teens & decadeword & Case(decade > 1 and year > 0; "-" & yearword; decade > 1;yearword)

)

 

EDIT: Not as elegant as comment's, but what else is new?