Convert date to words

[mukeshnic]

Hi,

Is there any way to convert stored dates into words? For example:
Column A : Column B
02/22/2012 : Twenty Two February, Two Thousand Tweleve

Thanks in advance

Regards
Mukesh

 

[Rick Rothstein]

Is there any way to convert stored dates into words? For example:
Column A : Column B
02/22/2012 : Twenty Two February, Two Thousand Tweleve

Here is something I have posted in the past which gives the day as an ordinal word rather than just a number word... perhaps you can make use of it instead...

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, " mmmm ") & _
                Cardinal(CInt(Left$(Yrs, 1))) & _
                " Thousand " & Hundreds & Decades
End Function

If you don't want the day as an ordinal word, you should be able to change the Ordinal array assignments to your liking (First would become One, Second would become Two and so on).

 

[repairman615]

Here is an UDF function that may be able to help. I didn't add anything for the Years and didn't complete all the days and months, however it should give an Idea:




<font face=Courier New><SPAN style="color:#00007F">Function</SPAN> ConvertDate(myDate) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br><br><SPAN style="color:#00007F">Dim</SPAN> ddd <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> mmm <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <br>    <SPAN style="color:#00007F">Select</SPAN> <SPAN style="color:#00007F">Case</SPAN> Mid(myDate, InStr(1, myDate, "/") + 1, InStr(InStr(1, myDate, "/"), myDate, "/"))<br>        <SPAN style="color:#00007F">Case</SPAN> "01"<br>            ddd = "One"<br>        <SPAN style="color:#00007F">Case</SPAN> "02" <SPAN style="color:#00007F">Or</SPAN> "2"<br>            ddd = "Two"<br>        <SPAN style="color:#00007F">Case</SPAN> "03"<br>            ddd = "Three"<br>        <SPAN style="color:#00007F">Case</SPAN> "04"<br>        <br>        <SPAN style="color:#00007F">Case</SPAN> "05"<br>        <br>        <SPAN style="color:#00007F">Case</SPAN> "06"<br>        <br>        <SPAN style="color:#00007F">Case</SPAN> "07"<br>        <br>        <SPAN style="color:#00007F">Case</SPAN> "08"<br>        <br>        <SPAN style="color:#00007F">Case</SPAN> "09"<br>        <br>        <SPAN style="color:#00007F">Case</SPAN> "10"<br>        <br>        <SPAN style="color:#00007F">Case</SPAN> "11"<br>        <br>        <SPAN style="color:#00007F">Case</SPAN> "22"<br>            ddd = "Twenty Two"<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Select</SPAN><br>     <br>    <SPAN style="color:#00007F">Select</SPAN> <SPAN style="color:#00007F">Case</SPAN> Left(myDate, InStr(1, myDate, "/") - 1)<br>        <SPAN style="color:#00007F">Case</SPAN> "01"<br>            mmm = "January"<br>        <SPAN style="color:#00007F">Case</SPAN> "02" <SPAN style="color:#00007F">Or</SPAN> "2"<br>            mmm = "Febuary"<br>        <SPAN style="color:#00007F">Case</SPAN> "03"<br>        <br>        <SPAN style="color:#00007F">Case</SPAN> "04"<br>        <br>        <SPAN style="color:#00007F">Case</SPAN> "05"<br>        <br>        <SPAN style="color:#00007F">Case</SPAN> "06"<br>        <br>        <SPAN style="color:#00007F">Case</SPAN> "07"<br>        <br>        <SPAN style="color:#00007F">Case</SPAN> "08"<br>        <br>        <SPAN style="color:#00007F">Case</SPAN> "09"<br>        <br>        <SPAN style="color:#00007F">Case</SPAN> "10"<br>        <br>        <SPAN style="color:#00007F">Case</SPAN> "11"<br>        <br>        <SPAN style="color:#00007F">Case</SPAN> "12"<br>        <br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Select</SPAN><br>    <br>ConvertDate = ddd & " " & mmm & ", Two Thousand Twelve"<br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN></FONT>

 

[mukeshnic]

Thank you so much Rick !!
It works like a dream

 

[talktime]

Thanks, its working, but what if i want to convert "One Thousand Nine Hundred" to only "N.H." for example:
01/01/1999 to First January, N.H. Ninety Nine.

 

[Rick Rothstein]

Thanks, its working, but what if i want to convert "One Thousand Nine Hundred" to only "N.H." for example:
01/01/1999 to First January, N.H. Ninety Nine.

I think the answer to that will depend on what you want to happen for dates whose years are greater than 1999.

 

[talktime]

the year greater than 1999 should work as it is working now mean, its fine to translate 1/1/2000 to First January, Two Thousand. i only want to abbreviate 19 to N.H. as i suggested in my previous post.

 

[Ganeszgk]

this coding is okay but i have one doubt, where i paste this coding and what is the next process. please can explain step by step process.

 

[Ganeszgk]

Here is an UDF function that may be able to help. I didn't add anything for the Years and didn't complete all the days and months, however it should give an Idea:




Function ConvertDate(myDate) As String

Dim ddd As String
Dim mmm As String
****
****Select Case Mid(myDate, InStr(1, myDate, "/") + 1, InStr(InStr(1, myDate, "/"), myDate, "/"))
********Case "01"
************ddd = "One"
********Case "02" Or "2"
************ddd = "Two"
********Case "03"
************ddd = "Three"
********Case "04"
********
********Case "05"
********
********Case "06"
********
********Case "07"
********
********Case "08"
********
********Case "09"
********
********Case "10"
********
********Case "11"
********
********Case "22"
************ddd = "Twenty Two"
****End Select
****
****Select Case Left(myDate, InStr(1, myDate, "/") - 1)
********Case "01"
************mmm = "January"
********Case "02" Or "2"
************mmm = "Febuary"
********Case "03"
********
********Case "04"
********
********Case "05"
********
********Case "06"
********
********Case "07"
********
********Case "08"
********
********Case "09"
********
********Case "10"
********
********Case "11"
********
********Case "12"
********
****End Select
****
ConvertDate = ddd & " " & mmm & ", Two Thousand Twelve"

End Function

please mention which formula for using B1 ??please help MrExcel users