Convert numeric date into words
Closed
rajeshwaran_k
TrowaD
- Posts
- 1
- Registration date
- Friday May 30, 2014
- Status
- Member
- Last seen
- May 30, 2014
TrowaD
- Posts
- 2886
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- June 27, 2022
Related:
- Convert numeric date into words
- How to convert numeric to words in excel - Guide
- How to convert a numeric value into english words in excel 2007 ✓ - Forum - Word
- Numeric convert in words - Guide
- Convert Indian currency numeric to words ✓ - Forum - Word
- How to convert a numeric value into English w ✓ - Forum - Office Software
1 reply
TrowaD
Jun 2, 2014 at 11:36 AM
- Posts
- 2886
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- June 27, 2022
Jun 2, 2014 at 11:36 AM
Hi Rajeshwaran_k,
Start by putting the following custom function in a module:
Now use the following formula structure to get desired result:
=words(DAY(A5))&" "&TEXT(A5;"MMMM")& " "&words(YEAR(A5))
Best regards,
Trowa
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