JOINING OF TEXT & DATE
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
|