Pete
OrdinalNumber is one of Chip Pearson's constructs.
http://www.cpearson.com/excel/ordinal.htm
Yes, the original was to give an ordinal for any number, not just dates.
Gord
On 5 Oct 2006 14:48:51 -0700, "Pete_UK" wrote:
I realise now (having looked at it in a bit more detail) that your
function will apply to numbers other than dates, which are limited to a
maximum of 31, but I was thinking along these lines when I posted (I've
just amended your UDF - hope you don't mind):
Function OrdinalNum(ByVal Num As Long) As String
Dim n As Long
Const cSfx =
"stndrdthththththththththththththththththstndrdth ththththththst"
n = Num Mod 100
OrdinalNum = Format(Num) & Mid(cSfx, (Abs(n) * 2) - 1, 2)
End Function
From this you can just get the appropriate pair of letters directly,
without the complex IF, though it is limited to a maximum value of 31.
It seems as if you have teeth left <bg
Pete
Gord Dibben wrote:
Not sure what you're getting at Pete.
Please expand. Remember.....I'm getting very long in the tooth<g
Gord
On 5 Oct 2006 13:35:07 -0700, "Pete_UK" wrote:
Hi Gord,
why not just make the string cSfx 62 characters long with all the
suffixes for 1 to 31 contained within it, and then pick the appropriate
pair?
Pete
Gord Dibben wrote:
With a UDF and this formula..........
="Receipts from: 1st to " & ordinalnumber(DAY(A1)) & " of " &TEXT(A1,"mmmm")
Function OrdinalNumber(ByVal Num As Long) As String
Dim n As Long
Const cSfx = "stndrdthththththth"
n = Num Mod 100
If ((Abs(n) = 10) And (Abs(n) <= 19)) _
Or ((Abs(n) Mod 10) = 0) Then
OrdinalNumber = Format(Num) & "th"
Else
OrdinalNumber = Format(Num) & Mid(cSfx, _
((Abs(n) Mod 10) * 2) - 1, 2)
End If
End Function
Gord Dibben MS Excel MVP
On Thu, 5 Oct 2006 15:17:13 -0400, "SJ" wrote:
Hello!
Text = "Receipts from:"
Date = 10/5/06. (This date will in a different cell and will change every
month)
The decription I desire will be: "Receipts from: "1st to 5th October" or any
variation thereof as long as it shows the period
Thanks
SJ