JOINING OF TEXT & DATE

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

SJ - 05 Oct 2006 20:17 GMT

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

Reply to this Message

Gary''s Student - 05 Oct 2006 20:42 GMT

If the date is in cell A1 then:
="Receipts from:" & TEXT(A1,"mm-dd-yyyy")

Signature

Gary's Student

> Hello!
>
[quoted text clipped - 7 lines]
> Thanks
> SJ

Reply to this Message

Pete_UK - 05 Oct 2006 21:18 GMT

You will need two dates (unless the start date is always the first of
the month) - assume these are in A1 and B1. Your formula is then:

="Receipts from: "&TEXT(A1,"mm/dd/yy")&" to "&TEXT(B1,"mm/dd/yy")

Hope this helps.

Pete

> Hello!
>
[quoted text clipped - 7 lines]
> Thanks
> SJ

Reply to this Message

Gord Dibben - 05 Oct 2006 21:25 GMT

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

>Hello!
>
[quoted text clipped - 7 lines]
>Thanks
>SJ

Reply to this Message

Pete_UK - 05 Oct 2006 21:35 GMT

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

> With a UDF and this formula..........
>
[quoted text clipped - 26 lines]
> >Thanks
> >SJ

Reply to this Message

Gord Dibben - 05 Oct 2006 22:02 GMT

Not sure what you're getting at Pete.

Please expand. Remember.....I'm getting very long in the tooth<g>

Gord

>Hi Gord,
>
[quoted text clipped - 34 lines]
>> >Thanks
>> >SJ

Reply to this Message

Gord Dibben - 05 Oct 2006 22:24 GMT

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

>Not sure what you're getting at Pete.
>
[quoted text clipped - 40 lines]
>>> >Thanks
>>> >SJ

Gord Dibben MS Excel MVP

Reply to this Message

Pete_UK - 05 Oct 2006 22:53 GMT

SJ emailed me directly to say:

> Thanks Pete!
>
[quoted text clipped - 4 lines]
>
> SJ

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

Pete

> I took SJ's request to mean always start at the 1st of each month so I
> hard-coded the "1st"
[quoted text clipped - 53 lines]
>
> Gord Dibben MS Excel MVP

Reply to this Message

Gord Dibben - 05 Oct 2006 23:04 GMT

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

>SJ emailed me directly to say:
>
[quoted text clipped - 69 lines]
>>
>> Gord Dibben MS Excel MVP

Reply to this Message

Pete_UK - 05 Oct 2006 22:48 GMT

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 =
"stndrdthththththththththththththththththstndrdthththththththst"
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

> Not sure what you're getting at Pete.
>
[quoted text clipped - 40 lines]
> >> >Thanks
> >> >SJ

Reply to this Message

Gord Dibben - 05 Oct 2006 23:03 GMT

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

>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
[quoted text clipped - 60 lines]
>> >> >Thanks
>> >> >SJ

Reply to this Message

SJ - 06 Oct 2006 12:51 GMT

Gord,

Your formula is what I was ideally looking for, however, as I am not sure
what UDF is and how to use the code that you had given below.

SJ

> With a UDF and this formula..........
>
[quoted text clipped - 28 lines]
>>Thanks
>>SJ

Reply to this Message

Gord Dibben - 06 Oct 2006 23:44 GMT

SJ

If not familiar with VBA and macros, see David McRitchie's site for more on
"getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

In the meantime..........

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + R to open Project Explorer.

Find your workbook/project and select it.

Right-click and Insert>Module. Paste the UDF code in there. Save the
workbook and hit ALT + Q to return to Excel window.

Enter the formula as shown in prior posts.

This UDF adds the ordinal to any number.

The number in this case is provided by the DAY(A1) part.

Gord

>Gord,
>
[quoted text clipped - 35 lines]
>>>Thanks
>>>SJ

Gord Dibben MS Excel MVP

Reply to this Message