To get from 1/4/2009 to "January 4th, 2009" or "4th of January, 2009" or even simply "January 4th" just got a little simpler. This date slicer/dicer takes a valid date and turns it into verbose syntax.

special thanks to UA VIP jwhite for pointing out an error with variants of 11, 12 & 13 not getting their "th" - now corrected - March 13, 2011

CODE

Public Function DateAsWords(dt As Date, Optional f As Integer = 0)

' Usage: DateAsWords(#1/4/2009#) or DateAsWords(#1/4/2009#,1)

' dt = a valid date - 'f' selects a format:

' DateAsWords(#1/4/2009#) = "4th"

' DateAsWords(#1/4/2009#,1) = "January 4th, 2009"

' DateAsWords(#1/4/2009#,2) = "4th of January, 2009"

' DateAsWords(#1/4/2009#,3) = "4th of January"

' Make sure your date value is wrapped in #'s or is a valid date variable

' oterwise, you'll just get "1 divided by 4 divided by 2009" (1.24e-4)

' So why do simple numbers, (not even formatted like a date), return a result? . . . .

'

' Access treats dates as 8 byte numbers and balances 12/30/1899

' as the zero point. (Dunno why) If you date format zero

' Format(0,"m/d/yyyy") = 12/30/1899.

' However, if you inadvertantly use only 3 'y's

' you will get: Format(0,"m/d/yyy") = [URL="tel:12/30/99364"]12/30/99364[/URL]

' So, today's numeric date value is basically the number of days

' since 12/30/1899.

' The formula works negatively too. If you Format(-179,"m/d/yyyy")

' you will get 7/4/1899

' -657434 is the negative limit (1/1/100) and 2958465 is the upper

' limit ([URL="tel:12/31/9999"]12/31/9999[/URL])

Select Case right(Day(dt), 1)

Case 1

If Day(dt) > 9 And Day(dt) < 14 Then

DateAsWords = Day(dt) & "th"

Else

DateAsWords = Day(dt) & "st"

End If

Case 2

If Day(dt) > 9 And Day(dt) < 14 Then

DateAsWords = Day(dt) & "th"

Else

DateAsWords = Day(dt) & "nd"

End If

Case 3

If Day(dt) > 9 And Day(dt) < 14 Then

DateAsWords = Day(dt) & "th"

Else

DateAsWords = Day(dt) & "rd"

End If

Case Else

DateAsWords = Day(dt) & "th"

End Select

Select Case f

Case 0

' Change nothing

Case 1

DateAsWords = Format(dt, "mmmm") & " " & DateAsWords & ", " & Year(dt)

Case 2

DateAsWords = DateAsWords & " of " & Format(dt, "mmmm") & ", " & Year(dt)

Case 3

DateAsWords = DateAsWords & " of " & Format(dt, "mmmm")

End Select

End Function

' Usage: DateAsWords(#1/4/2009#) or DateAsWords(#1/4/2009#,1)

' dt = a valid date - 'f' selects a format:

' DateAsWords(#1/4/2009#) = "4th"

' DateAsWords(#1/4/2009#,1) = "January 4th, 2009"

' DateAsWords(#1/4/2009#,2) = "4th of January, 2009"

' DateAsWords(#1/4/2009#,3) = "4th of January"

' Make sure your date value is wrapped in #'s or is a valid date variable

' oterwise, you'll just get "1 divided by 4 divided by 2009" (1.24e-4)

' So why do simple numbers, (not even formatted like a date), return a result? . . . .

'

' Access treats dates as 8 byte numbers and balances 12/30/1899

' as the zero point. (Dunno why) If you date format zero

' Format(0,"m/d/yyyy") = 12/30/1899.

' However, if you inadvertantly use only 3 'y's

' you will get: Format(0,"m/d/yyy") = [URL="tel:12/30/99364"]12/30/99364[/URL]

' So, today's numeric date value is basically the number of days

' since 12/30/1899.

' The formula works negatively too. If you Format(-179,"m/d/yyyy")

' you will get 7/4/1899

' -657434 is the negative limit (1/1/100) and 2958465 is the upper

' limit ([URL="tel:12/31/9999"]12/31/9999[/URL])

Select Case right(Day(dt), 1)

Case 1

If Day(dt) > 9 And Day(dt) < 14 Then

DateAsWords = Day(dt) & "th"

Else

DateAsWords = Day(dt) & "st"

End If

Case 2

If Day(dt) > 9 And Day(dt) < 14 Then

DateAsWords = Day(dt) & "th"

Else

DateAsWords = Day(dt) & "nd"

End If

Case 3

If Day(dt) > 9 And Day(dt) < 14 Then

DateAsWords = Day(dt) & "th"

Else

DateAsWords = Day(dt) & "rd"

End If

Case Else

DateAsWords = Day(dt) & "th"

End Select

Select Case f

Case 0

' Change nothing

Case 1

DateAsWords = Format(dt, "mmmm") & " " & DateAsWords & ", " & Year(dt)

Case 2

DateAsWords = DateAsWords & " of " & Format(dt, "mmmm") & ", " & Year(dt)

Case 3

DateAsWords = DateAsWords & " of " & Format(dt, "mmmm")

End Select

End Function

Great code. Works in Access 2010 too, but while "DateAsWords(Date(),1)" gave me "April 30th, 2013", I had to use "DateAsWords(Date(),0)" to get "30th"

Actually, "DateAsWords(Date(),1)" does deliver as advertised, rendering '**month day ^{xx}, year**'

sing 0 (instead of the 1) yields the same result as "DateAsWords(Date())" - because the numeric value defaults to zero (note the Optional "f" as Integer = 0 in the call line). So, you could leave the optional

Glad you like it!

CyberCow,

I made a couple of tweaks to the DateAsWords code as follows:

Dim tm As String

Select Case Right(Day(dt), 1)

Case 1

If Day(dt) > 9 And Day(dt) < 14 Then

DateAsWords = Day(dt) & "th"

Else

DateAsWords = Day(dt) & "st"

End If

Case 2

If Day(dt) > 9 And Day(dt) < 14 Then

DateAsWords = Day(dt) & "th"

Else

DateAsWords = Day(dt) & "st"

End If

Case 3

If Day(dt) > 9 And Day(dt) < 14 Then

DateAsWords = Day(dt) & "th"

Else

DateAsWords = Day(dt) & "nd"

End If

Case 4

If Day(dt) > 9 And Day(dt) < 14 Then

DateAsWords = Day(dt) & "th"

Else

DateAsWords = Day(dt) & "rd"

End If

Case Else

DateAsWords = Day(dt) & "th"

End Select

Select Case f

Case 0

' Change nothing

Case 1

DateAsWords = Format(dt, "mmmm") & " " & DateAsWords & ", " & Year(dt)

Case 2

DateAsWords = Format(dt, "dddd") & " " & Format(dt, "mmmm") & " " & DateAsWords & ", " & Year(dt)

Case 3

DateAsWords = DateAsWords & " of " & Format(dt, "mmmm") & ", " & Year(dt)

Case 4

DateAsWords = DateAsWords & " of " & Format(dt, "mmmm")

End Select

In my tweaks I made case 2 case 3 and case 3 case 4. I made case 2 and added the day of the week in the beginning. (Re: Thursday May 14th, 2015) for those who would want to show the day of the week before the date.

This is just my personal tweak.

- Charles

Actually, ordinal return is a rather simple matter. I reduced it to a single line of code using a Switch function, though for performance conditions you may wish to use Select Case or If conditionals. You can drop the function into your bag of tricks, or just use the single line code within your own procedures.

CODE

Public Function fncOrdinal(ByVal lngNumber As Long) As String

fncOrdinal = CStr(lngNumber) & Switch( _

lngNumber Mod 100 >= 11 And lngNumber Mod 100 <= 13, "th", _

lngNumber Mod 10 = 1, "st", _

lngNumber Mod 10 = 2, "nd", _

lngNumber Mod 10 = 3, "rd", _

-1, "th")

End Function

fncOrdinal = CStr(lngNumber) & Switch( _

lngNumber Mod 100 >= 11 And lngNumber Mod 100 <= 13, "th", _

lngNumber Mod 10 = 1, "st", _

lngNumber Mod 10 = 2, "nd", _

lngNumber Mod 10 = 3, "rd", _

-1, "th")

End Function