# Conversion of DOB to full words

Solved/Closed
jigs - Feb 20, 2016 at 09:06 PM
gane@1987 Posts 1 Registration date Friday March 3, 2017 Status Member Last seen March 3, 2017 - 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

## 4 responses

vcoolio Posts 1409 Registration date Thursday July 24, 2014 Status Moderator Last seen May 23, 2024 262
Feb 21, 2016 at 04:26 AM
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.

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 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))
Else
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.
Thank you friend I got my solution.
gane@1987 Posts 1 Registration date Friday March 3, 2017 Status Member Last seen March 3, 2017
Mar 3, 2017 at 10:12 AM
Function DateToWords(ByVal DateIn As Variant) As String
Dim Yrs As String
Dim Hundreds 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))
Else
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.