Here is something I have posted in the past which gives the day as an ordinal word rather than just a number word... perhaps you can make use of it instead...Is there any way to convert stored dates into words? For example:
Column A : Column B
02/22/2012 : Twenty Two February, Two Thousand Tweleve
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 "
Else
Hundreds = ""
End If
DateToWords = Ordinal(Day(DateIn) - 1) & _
Format$(DateIn, " mmmm ") & _
Cardinal(CInt(Left$(Yrs, 1))) & _
" Thousand " & Hundreds & Decades
End Function
I think the answer to that will depend on what you want to happen for dates whose years are greater than 1999.Thanks, its working, but what if i want to convert "One Thousand Nine Hundred" to only "N.H." for example:
01/01/1999 to First January, N.H. Ninety Nine.
Here is an UDF function that may be able to help. I didn't add anything for the Years and didn't complete all the days and months, however it should give an Idea:
Function ConvertDate(myDate) As String
Dim ddd As String
Dim mmm As String
****
****Select Case Mid(myDate, InStr(1, myDate, "/") + 1, InStr(InStr(1, myDate, "/"), myDate, "/"))
********Case "01"
************ddd = "One"
********Case "02" Or "2"
************ddd = "Two"
********Case "03"
************ddd = "Three"
********Case "04"
********
********Case "05"
********
********Case "06"
********
********Case "07"
********
********Case "08"
********
********Case "09"
********
********Case "10"
********
********Case "11"
********
********Case "22"
************ddd = "Twenty Two"
****End Select
****
****Select Case Left(myDate, InStr(1, myDate, "/") - 1)
********Case "01"
************mmm = "January"
********Case "02" Or "2"
************mmm = "Febuary"
********Case "03"
********
********Case "04"
********
********Case "05"
********
********Case "06"
********
********Case "07"
********
********Case "08"
********
********Case "09"
********
********Case "10"
********
********Case "11"
********
********Case "12"
********
****End Select
****
ConvertDate = ddd & " " & mmm & ", Two Thousand Twelve"
End Function