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
 

Some videos you may like

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,307
Office Version
  1. 2010
Platform
  1. Windows
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).
 

repairman615

Well-known Member
Joined
Dec 21, 2009
Messages
1,885
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>
 

talktime

New Member
Joined
May 18, 2010
Messages
6

ADVERTISEMENT

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:

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,307
Office Version
  1. 2010
Platform
  1. Windows
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

New Member
Joined
May 18, 2010
Messages
6

ADVERTISEMENT

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

New Member
Joined
Dec 25, 2016
Messages
13
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

New Member
Joined
Dec 25, 2016
Messages
13
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
 

Watch MrExcel Video

Forum statistics

Threads
1,108,950
Messages
5,525,827
Members
409,665
Latest member
Jessyi

This Week's Hot Topics

Top