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

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

I took SJ's request to mean always start at the 1st of each month so I
hard-coded the "1st"

If the start date is to be changed also and the two dates are in A1(start) and
A2(current), re-write as

="Receipts from: " & ordinalnumber(DAY(A1)) & " to " & ordinalnumber(DAY(A2)) &
" of " &TEXT(A1,"mmmm")


Gord

On Thu, 05 Oct 2006 14:02:14 -0700, Gord Dibben <gorddibbATshawDOTca 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


Gord Dibben MS Excel MVP

SJ emailed me directly to say:

Thanks Pete!

As the start date will always be the first of the month, I came up with
the following:

="Receipts from:1 - "&TEXT(A1,"d mmm yyyy")

SJ

So he got something out of it. I suggested that he continues to monitor
the thread.

Pete

Gord Dibben wrote:
I took SJ's request to mean always start at the 1st of each month so I
hard-coded the "1st"

If the start date is to be changed also and the two dates are in A1(start) and
A2(current), re-write as

="Receipts from: " & ordinalnumber(DAY(A1)) & " to " & ordinalnumber(DAY(A2)) &
" of " &TEXT(A1,"mmmm")


Gord

On Thu, 05 Oct 2006 14:02:14 -0700, Gord Dibben <gorddibbATshawDOTca 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


Gord Dibben MS Excel MVP

Thanks for the update Pete.

I take these requests too literally sometimes.

If SJ wants the "5th of October" we'll give it to him/her.


Gord

On 5 Oct 2006 14:53:01 -0700, "Pete_UK" wrote:

SJ emailed me directly to say:

Thanks Pete!

As the start date will always be the first of the month, I came up with
the following:

="Receipts from:1 - "&TEXT(A1,"d mmm yyyy")

SJ

So he got something out of it. I suggested that he continues to monitor
the thread.

Pete

Gord Dibben wrote:
I took SJ's request to mean always start at the 1st of each month so I
hard-coded the "1st"

If the start date is to be changed also and the two dates are in A1(start) and
A2(current), re-write as

="Receipts from: " & ordinalnumber(DAY(A1)) & " to " & ordinalnumber(DAY(A2)) &
" of " &TEXT(A1,"mmmm")


Gord

On Thu, 05 Oct 2006 14:02:14 -0700, Gord Dibben <gorddibbATshawDOTca 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


Gord Dibben MS Excel MVP

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 =
"stndrdthththththththththththththththththstndrdtht hthththththst"
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

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