Date to Words 2

[Dominican1979]

Hello everyone,

Thank you for reading my post. I am trying to convert a date into words sort of. Here's an example:

11/09/2010 convert to: 9th of November 2010
01/01/2010 convert to: 1st of January 2010

I've tried the function MonthName(month) for the month and works and the year Year(year) but i'm not sure how to get the day with either the st, nd, rd or th, etc after the day. Your help will be greatly appreciated.

Crystal Reports XI

 

[MCuthill]

Dominican,

Here is a formula to give you an idea as to where to start. I would think you may want to build a separate formula field for Day & Year to avoid dealing with decimals and commas in the Day and Year (without making a nested replace).

Raw Formula:

If Day({Table.Field}) in [1,21,31] THEN
    Day({Table.Field}) & "st of " & MonthName(Month({Table.Field})) & " " & Year({Table.Field})
ELSE
If Day({Table.Field}) in [2,22] THEN
    Day({Table.Field}) & "nd of " & MonthName(Month({Table.Field})) & " " & Year({Table.Field})
ELSE
If Day({Table.Field}) in [3,23] THEN
    Day({Table.Field}) & "rd of " & MonthName(Month({Table.Field})) & " " & Year({Table.Field})
ELSE
    Day({Table.Field}) & "th of " & MonthName(Month({Table.Field})) & " " & Year({Table.Field})

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[MCuthill]

My apologies, I hit submit before complete... oops.

{@Day}

Replace(ToText(Day({Table.Field})),".00","")

{@Year}

Replace(Replace(ToText(Year({Table.Field})),".00",""),",","")

{@Final Display}

If Day({Table.Field}) in [1,21,31] THEN
    {@Day} & "st of " & MonthName(Month({Table.Field})) & " " & {@Year}
ELSE
If Day({Table.Field}) in [2,22] THEN
    {@Day} & "nd of " & MonthName(Month({Table.Field})) & " " & {@Year}
ELSE
If Day({Table.Field}) in [3,23] THEN
    {@Day} & "rd of " & MonthName(Month({Table.Field})) & " " & {@Year}
ELSE
    {@Day} & "th of " & MonthName(Month({Table.Field})) & " " & {@Year}

{@Final Display} will return "23rd of November 2010" for today.

Hope this helps.

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[lbass]

totext(day({table.date}),"d")+
(
if day({table.date}) in 11 to 13 then
'th' else
(
select totext(day({table.date}),"00")[2]
case '1' : 'st'
case '2' : 'nd'
case '3' : 'rd'
default : 'th'
)
)

-LB

 

[MCuthill]

Thanks LB!

Much cleaner than my solution.

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[Dominican1979]

Hi lbass and MCuthill thank you so much for your help, both of your solutions work like a charm