jigs -
Feb 20, 2016 at 09:06 PM - Latest reply:
gane@1987
1
Posts
Friday March 3, 2017Registration date
March 3, 2017
Last seen
- Mar 3, 2017 at 10:12 AM

Hello Jigs,

This will require a UDF (User Defined Function), not just a simple formula.

Read the following post (post #2) by Rick Rothstein way back in 2012. He has supplied an excellent solution for the OP which will work for you also.

http://www.mrexcel.com/forum/excel-questions/615519-convert-date-words.html

Place the UDF in a standard module. I have modified it as follows to allow for proper English grammar and punctuation in the end result:-

If your date is in say, cell A1, place this formula in cell B1 (or wherever you like):

Read Rick's post carefully and remember that this is his work solely.

I hope that this helps.

Cheerio,

vcoolio.

This will require a UDF (User Defined Function), not just a simple formula.

Read the following post (post #2) by Rick Rothstein way back in 2012. He has supplied an excellent solution for the OP which will work for you also.

http://www.mrexcel.com/forum/excel-questions/615519-convert-date-words.html

Place the UDF in a standard module. I have modified it as follows to allow for proper English grammar and punctuation in the end result:-

Function DateToWords(ByVal DateIn As Variant) As String Dim Yrs As String Dim Hundreds As String Dim Decades As String Dim Tens As Variant Dim Ordinal As Variant Dim Cardinal As Variant Ordinal = 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") Cardinal = 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") DateIn = CDate(DateIn) Yrs = CStr(Year(DateIn)) Decades = Mid$(Yrs, 3) If CInt(Decades) < 20 Then Decades = Cardinal(CInt(Decades)) Else Decades = Tens(CInt(Left$(Decades, 1)) - 2) & " " & _ Cardinal(CInt(Right$(Decades, 1))) End If Hundreds = Mid$(Yrs, 2, 1) If CInt(Hundreds) Then Hundreds = Cardinal(CInt(Hundreds)) & " Hundred and " Else Hundreds = "" End If DateToWords = Ordinal(Day(DateIn) - 1) & " day of " & _ Format$(DateIn, "mmmm, ") & _ Cardinal(CInt(Left$(Yrs, 1))) & _ " Thousand, " & Hundreds & Decades End Function

If your date is in say, cell A1, place this formula in cell B1 (or wherever you like):

=DateToWords(A1)

Read Rick's post carefully and remember that this is his work solely.

I hope that this helps.

Cheerio,

vcoolio.

Hello Jigs,

You will only get one result per cell. To get more than one result, drag the formula down as far as needed, e.g. B1 dragged down to B20. There will only ever be one result per cell.

Cheerio,

vcoolio.

You will only get one result per cell. To get more than one result, drag the formula down as far as needed, e.g. B1 dragged down to B20. There will only ever be one result per cell.

Cheerio,

vcoolio.

Hello Jigs,

Have you tampered with the UDF?

Following is the link to my test work book:-

https://www.dropbox.com/s/46sytj1nfo9zx2q/Convert%20Date%20to%20Words%28completely%20using%20UDF%29.xlsm?dl=0

You'll see that whatever date you enter in Column A, the correct wording will appear in Column C. The UDF and the formula associated with it work perfectly.

As I said previously, you need to drag the formula down as far as needed in Column C (or whichever Column you require) to cover the required cells in Column A (or whichever Column you have your dates in). Just make sure that the correct cell is referenced in the formula. Don't leave blanks between cells.

Cheerio,

vcoolio.

Have you tampered with the UDF?

Following is the link to my test work book:-

https://www.dropbox.com/s/46sytj1nfo9zx2q/Convert%20Date%20to%20Words%28completely%20using%20UDF%29.xlsm?dl=0

You'll see that whatever date you enter in Column A, the correct wording will appear in Column C. The UDF and the formula associated with it work perfectly.

As I said previously, you need to drag the formula down as far as needed in Column C (or whichever Column you require) to cover the required cells in Column A (or whichever Column you have your dates in). Just make sure that the correct cell is referenced in the formula. Don't leave blanks between cells.

Cheerio,

vcoolio.

- Member requests are more likely to be responded to.
- Members can monitor the statuses of their requests from their account pages.
- A CCM membership gives you access to additional options.

Dim Yrs As String

Dim Hundreds As String

Dim Decades As String

Dim Tens As Variant

Dim Ordinal As Variant

Dim Cardinal As Variant

Ordinal = 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")

Cardinal = 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")

DateIn = CDate(DateIn)

Yrs = CStr(Year(DateIn))

Decades = Mid$(Yrs, 3)

If CInt(Decades) < 20 Then

Decades = Cardinal(CInt(Decades))

Else

Decades = Tens(CInt(Left$(Decades, 1)) - 2) & " " & _

Cardinal(CInt(Right$(Decades, 1)))

End If

Hundreds = Mid$(Yrs, 2, 1)

If CInt(Hundreds) Then

Hundreds = Cardinal(CInt(Hundreds)) & " Hundred and "

Else

Hundreds = ""

End If

DateToWords = Ordinal(Day(DateIn) - 1) & " day of " & _

Format$(DateIn, "mmmm, ") & _

Cardinal(CInt(Left$(Yrs, 1))) & _

" Thousand, " & Hundreds & Decades

End Function

If your date is in say, cell A1, place this formula in cell B1 (or wherever you like):

=DateToWords(A1)

A1-- 23/09/1995

B1--Twenty third day of September, One Thousand, Nine Hundred and Ninety Five.

I want this type of format.. please help how it work...

A1--22/02/2012

B1--Twenty Two February, Two Thousand Tweleve.

please help Dear users.