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
gane@1987 Posts 1 Registration date Friday March 3, 2017 Status Member Last seen March 3, 2017 - Mar 3, 2017 at 10:12 AM
Related:
- Dob in words online
- College brawl online - Download - Adult games
- How to get whatsapp verification code online - Guide
- Messenger audio downloader online - Guide
- We can't recover your account online because there are no other options on file for your account. ✓ - Yahoo Mail Forum
- Dob in words ✓ - Excel Forum
4 responses
vcoolio
Posts
1404
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 15, 2023
259
Feb 21, 2016 at 04:26 AM
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.
https://www.mrexcel.com/board/threads/convert-date-to-words.615519/
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:-
If your date is in say, cell A1, place this formula in cell B1 (or wherever you like):
Read Rick's post carefully and remember that this is his work solely.
I hope that this helps.
Cheerio,
vcoolio.
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.
https://www.mrexcel.com/board/threads/convert-date-to-words.615519/
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.
Feb 22, 2016 at 04:45 AM
Feb 23, 2016 at 07:34 AM
Mar 3, 2017 at 10:12 AM
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.