Date of birth in words [Solved/Closed]

Report
Posts
1
Registration date
Monday April 13, 2015
Status
Member
Last seen
April 13, 2015
-
Posts
2638
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
August 4, 2020
-
Hello,

How can I enter date of birth in words in Excel?

1 reply

Posts
2638
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
August 4, 2020
432
Hi Pinckroje,

Can you give an example of what you are looking for.

I'm guessing you are looking to turn:
20-4-15
into:
Twenty April two thousand and fifteen

For that implement the following code (put it in a standard module):
Public Numbers As Variant, Tens As Variant

 Sub SetNums()
 Numbers = 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")
 End Sub

 Function WordNum(MyNumber As Double) As String
 Dim DecimalPosition As Integer, ValNo As Variant, StrNo As String
 Dim NumStr As String, n As Integer, Temp1 As String, Temp2 As String
 ' This macro was written by Chris Mead - www.MeadInKent.co.uk

 If Abs(MyNumber) > 999999999 Then
 WordNum = "Value too large"
 Exit Function
 End If

 SetNums

 ' String representation of amount (excl decimals)
 NumStr = Right("000000000" & Trim(Str(Int(Abs(MyNumber)))), 9)
 ValNo = Array(0, Val(Mid(NumStr, 1, 3)), Val(Mid(NumStr, 4, 3)), Val(Mid(NumStr, 7, 3)))

 For n = 3 To 1 Step -1 'analyse the absolute number as 3 sets of 3 digits
 StrNo = Format(ValNo(n), "000")

 If ValNo(n) > 0 Then
 Temp1 = GetTens(Val(Right(StrNo, 2)))
 If Left(StrNo, 1) <> "0" Then
 Temp2 = Numbers(Val(Left(StrNo, 1))) & " hundred"
 If Temp1 <> "" Then Temp2 = Temp2 & " and "
 Else
 Temp2 = ""
 End If

 If n = 3 Then
 If Temp2 = "" And ValNo(1) + ValNo(2) > 0 Then Temp2 = "and "
 WordNum = Trim(Temp2 & Temp1)
 End If
 If n = 2 Then WordNum = Trim(Temp2 & Temp1 & " thousand " & WordNum)
 If n = 1 Then WordNum = Trim(Temp2 & Temp1 & " million " & WordNum)

 End If
 Next n

 NumStr = Trim(Str(Abs(MyNumber)))

 ' Values after the decimal place
 DecimalPosition = InStr(NumStr, ".")
 Numbers(0) = "Zero"
 If DecimalPosition > 0 And DecimalPosition < Len(NumStr) Then
 Temp1 = " point"
 For n = DecimalPosition + 1 To Len(NumStr)
 Temp1 = Temp1 & " " & Numbers(Val(Mid(NumStr, n, 1)))
 Next n
 WordNum = WordNum & Temp1
 End If

 If Len(WordNum) = 0 Or Left(WordNum, 2) = " p" Then
 WordNum = "Zero" & WordNum
 End If 
 End Function

 Function GetTens(TensNum As Integer) As String
 ' Converts a number from 0 to 99 into text.
 If TensNum <= 19 Then
 GetTens = Numbers(TensNum)
 Else
 Dim MyNo As String
 MyNo = Format(TensNum, "00")
 GetTens = Tens(Val(Left(MyNo, 1))) & " " & Numbers(Val(Right(MyNo, 1)))
 End If
 End Function


This is the complete WordNum User Defined Function.

Then use the following formula to show the date of birth placed in A1 into words:
=wordnum(Day(A1)) & " " & TEXT(A1,"mmmm") & " " & wordnum(YEAR(A1))

Best regards,
Trowa

--
Monday, Tuesday and Thursday are usually the days I'll respond. Bear this in mind when awaiting a reply.
3
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 3107 users have said thank you to us this month