Convert Date into words
 

Hi,

I want to Convert Any date in to Word like
19/06/2009 ---- Nineteenth of June Tow Thousand Nine
help me urgently

--
Regards,

Convert Date into words
 

"Abdul Shakeel" wrote in message
...

Hi,

I want to Convert Any date in to Word like
19/06/2009 ---- Nineteenth of June Tow Thousand Nine
help me urgently

--
Regards,


Since you are dealing with a limited amount of data ie one to thirty one,
january to december. I would think a few tables with the possible
individual values and a three step look up function would do the trick.
Mike

Convert Date into words
 

You might get something from this site.

http://support.microsoft.com/kb/213360








"Abdul Shakeel" wrote in message
...
Hi,

I want to Convert Any date in to Word like
19/06/2009 ---- Nineteenth of June Tow Thousand Nine
help me urgently

--
Regards,

Convert Date into words
 

Here is a function (modified to include the word "of" that you requested)
that I originally wrote and posted for the compiled version of VB but which
works fine in VBA as well...

Function DateToWords(ByVal DateIn As Variant) As String
Dim Yrs As String
Dim Hundreds As String
Dim Decades As String
Dim Tens As Variant
Dim Ordinal As Variant
Dim Cardinal As Variant
Ordinal = Array("First", "Second", "Third", _
"Fourth", "Fifth", "Sixth", _
"Seventh", "Eighth", "Nineth", _
"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-nineth", "Thirtieth", _
"Thirty-first")
Cardinal = Array("", "One", "Two", "Three", "Four", _
"Five", "Six", "Seven", "Eight", "Nine", _
"Ten", "Eleven", "Twelve", "Thirteen", _
"Fourteen", "Fifteen", "Sixteen", _
"Seventeen", "Eighteen", "Nineteen")
Tens = Array("Twenty", "Thirty", "Forty", "Fifty", _
"Sixty", "Seventy", "Eighty", "Ninety")
DateIn = CDate(DateIn)
Yrs = CStr(Year(DateIn))
Decades = Mid$(Yrs, 3)
If CInt(Decades) < 20 Then
Decades = Cardinal(CInt(Decades))
Else
Decades = Tens(CInt(Left$(Decades, 1)) - 2) & "-" & _
Cardinal(CInt(Right$(Decades, 1)))
End If
Hundreds = Mid$(Yrs, 2, 1)
If CInt(Hundreds) Then
Hundreds = Cardinal(CInt(Hundreds)) & " Hundred "
Else
Hundreds = ""
End If
DateToWords = Ordinal(Day(DateIn) - 1) & _
Format$(DateIn, " of mmmm ") & _
Cardinal(CInt(Left$(Yrs, 1))) & _
" Thousand " & Hundreds & Decades
End Function

--
Rick (MVP - Excel)


"Abdul Shakeel" wrote in message
...
Hi,

I want to Convert Any date in to Word like
19/06/2009 ---- Nineteenth of June Tow Thousand Nine
help me urgently

--
Regards,

Convert Date into words
 

WOW!!!

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Rick Rothstein" wrote:

Here is a function (modified to include the word "of" that you requested)
that I originally wrote and posted for the compiled version of VB but which
works fine in VBA as well...

Function DateToWords(ByVal DateIn As Variant) As String
Dim Yrs As String
Dim Hundreds As String
Dim Decades As String
Dim Tens As Variant
Dim Ordinal As Variant
Dim Cardinal As Variant
Ordinal = Array("First", "Second", "Third", _
"Fourth", "Fifth", "Sixth", _
"Seventh", "Eighth", "Nineth", _
"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-nineth", "Thirtieth", _
"Thirty-first")
Cardinal = Array("", "One", "Two", "Three", "Four", _
"Five", "Six", "Seven", "Eight", "Nine", _
"Ten", "Eleven", "Twelve", "Thirteen", _
"Fourteen", "Fifteen", "Sixteen", _
"Seventeen", "Eighteen", "Nineteen")
Tens = Array("Twenty", "Thirty", "Forty", "Fifty", _
"Sixty", "Seventy", "Eighty", "Ninety")
DateIn = CDate(DateIn)
Yrs = CStr(Year(DateIn))
Decades = Mid$(Yrs, 3)
If CInt(Decades) < 20 Then
Decades = Cardinal(CInt(Decades))
Else
Decades = Tens(CInt(Left$(Decades, 1)) - 2) & "-" & _
Cardinal(CInt(Right$(Decades, 1)))
End If
Hundreds = Mid$(Yrs, 2, 1)
If CInt(Hundreds) Then
Hundreds = Cardinal(CInt(Hundreds)) & " Hundred "
Else
Hundreds = ""
End If
DateToWords = Ordinal(Day(DateIn) - 1) & _
Format$(DateIn, " of mmmm ") & _
Cardinal(CInt(Left$(Yrs, 1))) & _
" Thousand " & Hundreds & Decades
End Function

--
Rick (MVP - Excel)


"Abdul Shakeel" wrote in message
...
Hi,

I want to Convert Any date in to Word like
19/06/2009 ---- Nineteenth of June Tow Thousand Nine
help me urgently

--
Regards,