How do I add st, rd, nd suffix to number?

[FrankRomero]

I want to add the suffix of the number of the date so that it reads "Monday, November 22nd". Is there an easy way to do this? thanks!

 

[boblarson]

Check this out here.

 

[John Big Booty]

I found this suggestion Here;

Function EzSuffix(ByVal DateNum As Integer) As String

    Dim Ar() As String
    Ar = Split("st,nd,rd,th,th,th,th,th,th,th," & _
               "th,th,th,th,th,th,th,th,th,th," & _
               "st,nd,rd,th,th,th,th,th,th,th,st", ",")
    
    EzSuffix = Ar(DateNum - 1)
End Function

then use;

=[DateNum] & EzSuffix([DateNum])

 

[boblarson]

Actually, I am posting the code that was there at:


because it is formatted such that just copying it into a standard module doesn't seem to work so well. So here it is formatted so you can pop it into a standard module (remember to name the module something OTHER than the name of the function):

''' <summary>
''' Adds the ability to include the ordinal suffix for numbers, ie.,st, nd, rd, th
''' <summary>
' from MrSlimm here:
'[URL]http://misterslimm.wordpress.com/2009/02/13/ordinal-suffixes-for-all-dates-and-numbers-show-3rd-instead-of-3/[/URL]
 
Public Function FormatOrdinal(Number) As String
 
' Set a default return value
    FormatOrdinal = (Number)
 
    ' Only add suffix if a whole numeric value was supplied
    If IsNumeric(Number) Then
 
        ' Make sure the variant Number is now of a numeric data-type so we can perform
        ' numerical comparisons
        Number = Val(Number)
 
        If (Number = Int(Number)) And (Number <> 0) Then
            ' Use the last two digits of the number (between 0 and 99) for determining
            ' the suffix. We only use the whole part of the number (Int) and we we use
            ' the Abs function to make sure it is in the range 0 to 99. It is converted
            ' to a string (Format), the right two characters pulled off (Right$) and
            ' converted back to a number (Val).
            Dim Remainder As Long
            Remainder = Val(Right$(Format$(Int(Abs(Number))), 2))
 
            ' 2 character suffixes for numbers ending in 1 to 9 respectively
            Const Suffixes = "st" & _
                  "nd" & _
                  "rd" & _
                  "th" & _
                  "th" & _
                  "th" & _
                  "th" & _
                  "th" & _
                  "th"
 
            ' Suffix is "th" if remainder is between 10 and 19 or if it is exactly
            ' divisible by 10
            If ((Remainder >= 10) And (Remainder <= 19)) Or ((Remainder Mod 10) = 0) Then
                FormatOrdinal = Format$(Number) & "th"
            Else
                ' Pull suffix from constant Suffixes using the last digit doubled ((Remainder Mod 10) * 2)
                ' as a starting point
                FormatOrdinal = Format$(Number) & Mid$(Suffixes, ((Remainder Mod 10) * 2) - 1, 2)
            End If
        End If    ' Number = Int(Number)
    End If    ' IsNumeric(Number)
 
End Function

 

[boblarson]

I found this suggestion Here;

Function EzSuffix(ByVal DateNum As Integer) As String
 
    Dim Ar() As String
    Ar = Split("st,nd,rd,th,th,th,th,th,th,th," & _
               "th,th,th,th,th,th,th,th,th,th," & _
               "st,nd,rd,th,th,th,th,th,th,th,st", ",")
 
    EzSuffix = Ar(DateNum - 1)
End Function

then use;

=[DateNum] & EzSuffix([DateNum])

That should work (shorter too) if you are ONLY concerned with dates. If you want a function that works with all numbers then the one I posted will do that.

 

[John Big Booty]

That should work (shorter too) if you are ONLY concerned with dates. If you want a function that works with all numbers then the one I posted will do that.

I guess, between us, we have all contingencies covered then

 

[FrankRomero]

Yep. You guys are awesome. I will try both of these tomorrow when I can sit down with my query. I will keep you posted. I'm kinda slow.

 

[John Big Booty]

Bob, a supplementary question for you; Is there any way to get the numerator to show as superscript?

 

[boblarson]

Bob, a supplementary question for you; Is there any way to get the numerator to show as superscript?

That would only be possible using a Rich Text Box (or a separate control with the font size modified and set up against the original).

 

[John Big Booty]

Thanks Bob, I thought that would be the case, but thought it a question worth asking