How to Convert Age or Date of Birth from Digits to Words?

[ZKAHADI]

i am beginner dont understand coding very well, i saw a post in which i get code but i dont know it will work or now and i dont know how to use it in VBA. can anybody teach me ?

Option Compare Database
Option Explicit
 
Sub Test()
Dim TestDate As Date
    TestDate = CDate("11.04.2013")
Dim dtString As String
    dtString = DateInWords(TestDate)
 
    MsgBox (dtString)
End Sub
 
Function DateInWords(dt As Date) As String
Dim strDays As String, SplitDays
    strDays = _
        "Day_1,Day_2,Day_3,Day_4,Day_5,Day_6,Day_7," & _
        "Day_8,Day_9,Day_10,Day_11,Day_12,Day_13,Day_14," & _
        "Day_15,Day_16,Day_17,Day_18,Day_19,Day_20,Day_21," & _
        "Day_22,Day_23,Day_24,Day_25,Day_26,Day_27,Day_28," & _
        "Day_29,Day_30,Day_31"
    SplitDays = Split(strDays, ",")
Dim strMothes As String, SplitMonthes
    strMothes = _
        "Month_1,Month_2,Month_3,Month_4,Month_5,Month_6," & _
        "Month_7,Month_8,Month_9,Month_10,Month_11,Month_12"
    SplitMonthes = Split(strMothes, ",")
Dim strYears As String, SplitYears
    strYears = _
        "Year_2010,Year_2011,Year_2012,Year_2013," & _
        "Year_2014,Year_2015,Year_2016,Year_2017," & _
        "Year_2018,Year_2019,Year_2020"
    SplitYears = Split(strYears, ",")
 
Dim strDay As String
    strDay = SplitDays(Day(dt) - 1)
Dim strMonth As String
    strMonth = SplitMonthes(Month(dt) - 1)
Dim strYear As String
    strYear = SplitYears(Year(dt) - 2010)
 
    DateInWords = strDay & " " & strMonth & ", " & strYear
End Function

[SioSio]

Line #5
When using CDate, separate the year, month, and day of the date with a slash. Or write # before and after the date without using CDate.

TestDate = CDate("11/04/2013")

or

TestDate = #11/4/2013#

[ZKAHADI]

Brother i am asking about how to convert Date digits into Words like 1/1/2012 to First January Two Thousand Two

[SioSio]

Only the date conversion has adopted your way.
The Month is converted by the Format function, and the year is modified by the SpellNumber function provided by M.S.

Sub Test()
Dim TestDate As Date
TestDate = CDate("11/04/2013")
Dim dtString As String
dtString = DateInWords(TestDate)
MsgBox (dtString)
End Sub
 
 
Function DateInWords(dt As Date) As String
Dim strDays As String, SplitDays
strDays = "first,second,third,fourth,fifth," & _
    "sixth,seventh,eighth,ninth,tenth,11th,12th," & _
    "13th,14th,15th,16th,17th,18th,19th,20th," & _
    "21st,22nd,23rd,24th,25th,26th,27th,28th," & _
    "29th,30th,31th"
SplitDays = Split(strDays, ",")
 
Dim strDay As String
strDay = SplitDays(Day(dt) - 1)
 
Dim strMonth As String
strMonth = Format(dt, "mmmm")
 
Dim strYear As String
 
Dim intYear As Integer
Dim yyyy, y1, y2, y3 As Integer
strYear = Format(dt, "yyyy")
 
strYear = SpellNumber(strYear)
 
DateInWords = strDay & " " & strMonth & ", " & strYear
End Function
 
 
Function SpellNumber(MyNumber)
Dim Dollars, Cents, Temp
Dim DecimalPlace, Count
ReDim Place(9) As String
Place(2) = " Thousand "
Place(3) = " Million "
Place(4) = " Billion "
Place(5) = " Trillion "
 
' String representation of amount.
 
MyNumber = Trim(Str(MyNumber))
' Position of decimal place 0 if none.
DecimalPlace = InStr(MyNumber, ".")
Count = 1
Do While MyNumber <> ""
Temp = GetHundreds(Right(MyNumber, 3))
If Temp <> "" Then Dollars = Temp & Place(Count) & Dollars
If Len(MyNumber) > 3 Then
MyNumber = Left(MyNumber, Len(MyNumber) - 3)
Else
MyNumber = ""
End If
Count = Count + 1
Loop
SpellNumber = Dollars & Cents
End Function
 
' Converts a number from 100-999 into text
Function GetHundreds(ByVal MyNumber)
Dim Result As String
If Val(MyNumber) = 0 Then Exit Function
MyNumber = Right("000" & MyNumber, 3)
' Convert the hundreds place.
If Mid(MyNumber, 1, 1) <> "0" Then
Result = GetDigit(Mid(MyNumber, 1, 1)) & " Hundred "
End If
 
' Convert the tens and ones place.
If Mid(MyNumber, 2, 1) <> "0" Then
Result = Result & GetTens(Mid(MyNumber, 2))
Else
Result = Result & GetDigit(Mid(MyNumber, 3))
End If
GetHundreds = Result
End Function
 
' Converts a number from 10 to 99 into text.
Function GetTens(TensText)
Dim Result As String
Result = "" ' Null out the temporary function value.
If Val(Left(TensText, 1)) = 1 Then ' If value between 10-19...
Select Case Val(TensText)
Case 10: Result = "Ten"
Case 11: Result = "Eleven"
Case 12: Result = "Twelve"
Case 13: Result = "Thirteen"
Case 14: Result = "Fourteen"
Case 15: Result = "Fifteen"
Case 16: Result = "Sixteen"
Case 17: Result = "Seventeen"
Case 18: Result = "Eighteen"
Case 19: Result = "Nineteen"
Case Else
End Select
Else ' If value between 20-99...
Select Case Val(Left(TensText, 1))
Case 2: Result = "Twenty "
Case 3: Result = "Thirty "
Case 4: Result = "Forty "
Case 5: Result = "Fifty "
Case 6: Result = "Sixty "
Case 7: Result = "Seventy "
Case 8: Result = "Eighty "
Case 9: Result = "Ninety "
Case Else
End Select
Result = Result & GetDigit(Right(TensText, 1)) ' Retrieve ones place.
End If
GetTens = Result
End Function
 
 
' Converts a number from 1 to 9 into text.
 
Function GetDigit(Digit)
Select Case Val(Digit)
Case 1: GetDigit = "One"
Case 2: GetDigit = "Two"
Case 3: GetDigit = "Three"
Case 4: GetDigit = "Four"
Case 5: GetDigit = "Five"
Case 6: GetDigit = "Six"
Case 7: GetDigit = "Seven"
Case 8: GetDigit = "Eight"
Case 9: GetDigit = "Nine"
Case Else: GetDigit = ""
End Select
End Function
 

[ZKAHADI]

yea i need this kind of code. now please last time tell me where i use this code what shold i do first make a table with text boxdate and date in words then make form and where use this code in vba in class module oron date in words textbox?

[NeoPa]

I think I should jump in here to clarify what is expected, and even what is acceptable, for those who may not have read our very prominent guidelines.

This question is answered and it is not acceptable to allow it to meander on with further questions - related or otherwise.

This forum is for asking questions. It is not for hand-holding or for others to do work for you. Some users may decide to give further support than what is generally expected but that should never be assumed.

While those entirely new to the subject are welcome, they are expected to put the work in to progress from that so that they can understand both the questions and the answers. Where possible we encourage all our experts to give clear answers and to pitch it easily for newbies if required, but still the member has to be able to understand the basics. That is their responsibility, not the experts'.

It is easy to find many tutorials, and even very reasonably priced tutors, after a quick search so please understand this is not somewhere to come to allow others to do your work for you. You have to show at least as much effort as those assisting, and it should be more - except as you can see many of our experts do put a lot of effort into trying to help you so that bar's quite high.

As far as this thread goes the single question has been answered and further meandering will not be allowed.

[ZKAHADI]

never mind Neopa and Thanks SioSio this code working and i learned using thanks you very very much.