Convert date to words

mukeshnic

New Member
Joined
Mar 26, 2009
Messages
19
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
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
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...

Code:
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).
 
Upvote 0
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>
 
Upvote 0
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.
 
Last edited:
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,150
Members
448,552
Latest member
WORKINGWITHNOLEADER

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top