Convert Date into words

Discussion in 'Microsoft Excel Programming' started by Abdul Shakeel, Jun 26, 2009.

  1. Hi,

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

    --
    Regards,
     
    Abdul Shakeel, Jun 26, 2009
    #1
    1. Advertisements

  2. Abdul Shakeel

    JLGWhiz Guest

    You might get something from this site.

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








    "Abdul Shakeel" <> wrote in message
    news:...
    > Hi,
    >
    > I want to Convert Any date in to Word like
    > 19/06/2009 ---- Nineteenth of June Tow Thousand Nine
    > help me urgently
    >
    > --
    > Regards,
    >
     
    JLGWhiz, Jun 26, 2009
    #2
    1. Advertisements

  3. 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
    news:...
    > Hi,
    >
    > I want to Convert Any date in to Word like
    > 19/06/2009 ---- Nineteenth of June Tow Thousand Nine
    > help me urgently
    >
    > --
    > Regards,
    >
     
    Rick Rothstein, Jun 26, 2009
    #3
  4. Abdul Shakeel

    ryguy7272 Guest

    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
    > news:...
    > > Hi,
    > >
    > > I want to Convert Any date in to Word like
    > > 19/06/2009 ---- Nineteenth of June Tow Thousand Nine
    > > help me urgently
    > >
    > > --
    > > Regards,
    > >

    >
    >
     
    ryguy7272, Jun 27, 2009
    #4
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Guest

    HOW I CAN CONVERT NUMERICAL INTO WORDS IN CELL

    Guest, Oct 3, 2004, in forum: Microsoft Excel Programming
    Replies:
    1
    Views:
    314
    Ron de Bruin
    Oct 3, 2004
  2. Guest

    number convert into words

    Guest, Jan 15, 2005, in forum: Microsoft Excel Programming
    Replies:
    2
    Views:
    209
    Ron Rosenfeld
    Jan 16, 2005
  3. Guest

    number convert into words

    Guest, Jan 15, 2005, in forum: Microsoft Excel Programming
    Replies:
    1
    Views:
    228
    Tom Ogilvy
    Jan 15, 2005
  4. Guest
    Replies:
    1
    Views:
    245
    Juan Pablo González
    Feb 9, 2005
  5. Guest
    Replies:
    0
    Views:
    257
    Guest
    Mar 28, 2007
Loading...

Share This Page