1st, 2nd, 3rd, 4th, 5th ect Day 1

[waytech2003]

I have beeen looking for a function to return the Date as
"7th day of November 2009"

The Month and Year I can get from Format(Date,"MMMM YYYY") but how can I format the Day as 1st, 2nd, 3rd, 4th, 5th etc.

using Format(DATE,"D MMMM YYYY") returns "1 November 2009".

How can I add "st", "nd", "rd", "th" to the Day I am working with?

 

[waytech2003]

I found my answer via a Google search. It is on this page. So far it seems to work correctly.

http://www.downloadfreescript.com/s...-nunn-in-string-manipulation-visual-basic.htm

Public Function FormatFancyNumber(ByVal sNumber As String) As String
 Dim iTemp As Integer
 iTemp = Int(sNumber)
 If 4 < iTemp And iTemp < 20 Then
  FormatFancyNumber = sNumber & "th"
 Else
  Select Case iTemp Mod 10
   Case 1: FormatFancyNumber = sNumber & "st"
   Case 2: FormatFancyNumber = sNumber & "nd"
   Case 3: FormatFancyNumber = sNumber & "rd"
   Case Else: FormatFancyNumber = sNumber & "th"
  End Select
 End If
End Function

 

[AndyGroom]

Here's an alternate way I wrote a while ago:

Function Ending(A&) as String
  if (A& Mod 100) < 11 or (A& Mod 100) > 13 Then R$ = Choose(A& Mod 10, "st", "nd", "rd") & ""
  Ending = Iif(R$ = "", "th", R$)
End Function

The advantage of this method is that it works for any number, not just dates which have the range 1 to 31, so for example Ending(10022) returns "nd".

- Andy
___________________________________________________________________
If you think nobody cares you're alive, try missing a couple of mortgage payments

 

[waytech2003]

Thanks Andy, I like yours better and will use that