Conversion of DOB to full words [Solved/Closed]

jigs - Feb 20, 2016 at 09:06 PM - Latest reply: gane@1987 1 Posts Friday March 3, 2017Registration date March 3, 2017 Last seen
- Mar 3, 2017 at 10:12 AM
Hello,





I want to to convert dob to word like 5/6/2014 to fifth June two thousand fourteen please tell me solution
See more 

9 replies

vcoolio 1173 Posts Thursday July 24, 2014Registration dateModeratorStatus July 15, 2018 Last seen - Feb 21, 2016 at 04:26 AM
0
Thank you
Hello Jigs,

This will require a UDF (User Defined Function), not just a simple formula.

Read the following post (post #2) by Rick Rothstein way back in 2012. He has supplied an excellent solution for the OP which will work for you also.

http://www.mrexcel.com/forum/excel-questions/615519-convert-date-words.html

Place the UDF in a standard module. I have modified it as follows to allow for proper English grammar and punctuation in the end result:-


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 and "
  Else
    Hundreds = ""
  End If
  DateToWords = Ordinal(Day(DateIn) - 1) & " day of " & _
                Format$(DateIn, "mmmm, ") & _
                Cardinal(CInt(Left$(Yrs, 1))) & _
                " Thousand, " & Hundreds & Decades
End Function


If your date is in say, cell A1, place this formula in cell B1 (or wherever you like):
=DateToWords(A1)


Read Rick's post carefully and remember that this is his work solely.

I hope that this helps.

Cheerio,
vcoolio.
Hello while using this Formula I got oñly one answer please help me to solve this error
Thank you friend I got my solution.
gane@1987 1 Posts Friday March 3, 2017Registration date March 3, 2017 Last seen - Mar 3, 2017 at 10:12 AM
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 and "
Else
Hundreds = ""
End If
DateToWords = Ordinal(Day(DateIn) - 1) & " day of " & _
Format$(DateIn, "mmmm, ") & _
Cardinal(CInt(Left$(Yrs, 1))) & _
" Thousand, " & Hundreds & Decades
End Function



If your date is in say, cell A1, place this formula in cell B1 (or wherever you like):

=DateToWords(A1)


A1-- 23/09/1995

B1--Twenty third day of September, One Thousand, Nine Hundred and Ninety Five.

I want this type of format.. please help how it work...

A1--22/02/2012
B1--Twenty Two February, Two Thousand Tweleve.

please help Dear users.
vcoolio 1173 Posts Thursday July 24, 2014Registration dateModeratorStatus July 15, 2018 Last seen - Feb 22, 2016 at 05:06 AM
0
Thank you
Hello Jigs,

You will only get one result per cell. To get more than one result, drag the formula down as far as needed, e.g. B1 dragged down to B20. There will only ever be one result per cell.

Cheerio,
vcoolio.
No friends i'm saying that i'm finding only one answer for any date I enterd like for 7/10/1993 I got any other answer and for other date answer remain same
vcoolio 1173 Posts Thursday July 24, 2014Registration dateModeratorStatus July 15, 2018 Last seen - Feb 23, 2016 at 12:54 AM
0
Thank you
Hello Jigs,

Have you tampered with the UDF?

Following is the link to my test work book:-

https://www.dropbox.com/s/46sytj1nfo9zx2q/Convert%20Date%20to%20Words%28completely%20using%20UDF%29.xlsm?dl=0

You'll see that whatever date you enter in Column A, the correct wording will appear in Column C. The UDF and the formula associated with it work perfectly.

As I said previously, you need to drag the formula down as far as needed in Column C (or whichever Column you require) to cover the required cells in Column A (or whichever Column you have your dates in). Just make sure that the correct cell is referenced in the formula. Don't leave blanks between cells.

Cheerio,
vcoolio.
I have another problem that is I want to make all words capital and I have completed 90% of work on it but only month name is not becoming capital so please help me on this issue.
vcoolio 1173 Posts Thursday July 24, 2014Registration dateModeratorStatus July 15, 2018 Last seen - Feb 26, 2016 at 04:51 AM
0
Thank you
Hello Jigs,

If you want the month name completely in capitals, then change line 45 of
the code above to:-

UCase(Format$(DateIn, "mmmm, ")) & _


The rest you seem to have sorted out yourself.

Cheerio,
vcoolio.