How to Quickly convert date to words in Excel?
In general, we usually convert the date to other date formats or digits in Excel, but have you ever encountered a problem about converting the date to English words as below screenshot shown? Actually, there is no built-in function that can handle it but a VBA code.
Convert date to word with Defined Function
Convert date to word with Defined Function
Here is a macro code that can do you a favor on converting dates to words.
1. Enable the sheet you use and press Alt + F11 keys to open Microsoft Visual Basic for Applications window.
2. Click Insert > Module and paste below code to the script.
VBA: Convert date to word
Function DateToWords(ByVal xRgVal As Date) As String
'UpdatebyExtendoffice20170926
Dim xYear As String
Dim Hundreds As String
Dim Decades As String
Dim xTensArr As Variant
Dim xOrdArr As Variant
Dim xCardArr As Variant
xOrdArr = 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")
xCardArr = Array("", "One", "Two", "Three", "Four", _
"Five", "Six", "Seven", "Eight", "Nine", _
"Ten", "Eleven", "Twelve", "Thirteen", _
"Fourteen", "Fifteen", "Sixteen", _
"Seventeen", "Eighteen", "Nineteen")
xTensArr = Array("Twenty", "Thirty", "Forty", "Fifty", _
"Sixty", "Seventy", "Eighty", "Ninety")
xYear = CStr(Year(xRgVal))
Decades = Mid$(xYear, 3)
If CInt(Decades) < 20 Then
Decades = xCardArr(CInt(Decades))
Else
Decades = xTensArr(CInt(Left$(Decades, 1)) - 2) & "-" & _
xCardArr(CInt(Right$(Decades, 1)))
End If
Hundreds = Mid$(xYear, 2, 1)
If CInt(Hundreds) Then
Hundreds = xCardArr(CInt(Hundreds)) & " Hundred "
Else
Hundreds = ""
End If
DateToWords = xOrdArr(Day(xRgVal) - 1) & _
Format$(xRgVal, " mmmm ") & _
xCardArr(CInt(Left$(xYear, 1))) & _
" Thousand " & Hundreds & Decades
End Function
3. Save the code and go back to the sheet, select a cell which you will output the result in, type this formula =DateToWords(A1) (A1 is the date you use), press Enter key and drag auto fill handle to over the cells you need. See screenshot:
Numbers to Words |
 |
Relative Articles:
- How To Quickly Convert Date Format Between European And US In Excel?
- How to convert dD.MM.YYYY to date format (mM/DD/YYYY) in Excel?
- How To Convert Between Date And Unix Timestamp In Excel?
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
