Convert numeric date into words

Closed
rajeshwaran_k Posts 1 Registration date Friday May 30, 2014 Status Member Last seen May 30, 2014 - May 30, 2014 at 11:44 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Jun 2, 2014 at 11:36 AM
hi want to know how to convert date into words(english) in excel 2003 or 2007
for eg. 22.04.2010 - convert it into Twenty Two April 2010 or Twenty Two April Two Thousand Ten.
Thanks

1 response

TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Jun 2, 2014 at 11:36 AM
Hi Rajeshwaran_k,

Start by putting the following custom function in a module:
Function words(fig, Optional point = "Point") As String
Dim digit(14) As Integer
alpha = Array("", "One", "Two", "Three", "Four", "Five", "Six", "Seven", "Eight", "Nine", "Ten", "Eleven", "Twelve", "Thirteen", "Fourteen", "Fifteen", "Sixteen", "Seventeen", "Eighteen", "Nineteen", "Twenty", "Thirty", "Forty", "Fifty", "Sixty", "Seventy", "Eighty", "Ninety")
figi = Trim(StrReverse(Str(Int(Abs(fig)))))
For i = 1 To Len(figi)
digit(i) = Mid(figi, i, 1)
Next
For i = 2 To Len(figi) Step 3
If digit(i) = 1 Then
digit(i) = digit(i - 1) + 10: digit(i - 1) = 0
Else: If digit(i) > 1 Then digit(i) = digit(i) + 18
End If
Next
For i = 1 To Len(figi)
If (i Mod 3) = 0 And digit(i) > 0 Then words = "hundred " & words
If (i Mod 3) = 1 And digit(i) + digit(i + 1) + digit(i + 2) > 0 Then _
words = Choose(i / 3, "thousand ", "million ", "billion ") & words
words = Trim(alpha(digit(i)) & " " & words)
Next
If fig <> Int(fig) Then
figc = StrReverse(figi)
If figc = 0 Then figc = ""
figd = Trim(WorksheetFunction.Substitute(Str(Abs(fig)), figc & ".", ""))
words = Trim(words & " " & point)
For i = 1 To Len(figd)
If Val(Mid(figd, i, 1)) > 0 Then
words = words & " " & alpha(Mid(figd, i, 1))
Else: words = words & " Zero"
End If
Next
End If
If fig < 0 Then words = "Negative " & words
End Function


Now use the following formula structure to get desired result:
=words(DAY(A5))&" "&TEXT(A5;"MMMM")& " "&words(YEAR(A5))

Best regards,
Trowa
0