Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
JOINING OF TEXT & DATE
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 |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
JOINING OF TEXT & DATE
If the date is in cell A1 then:
="Receipts from:" & TEXT(A1,"mm-dd-yyyy") -- Gary's Student "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 |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
JOINING OF TEXT & DATE
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 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 |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
JOINING OF TEXT & DATE
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 |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
JOINING OF TEXT & DATE
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 |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
JOINING OF TEXT & DATE
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 |
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
JOINING OF TEXT & DATE
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 |
#8
Posted to microsoft.public.excel.newusers
|
|||
|
|||
JOINING OF TEXT & DATE
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 |
#9
Posted to microsoft.public.excel.newusers
|
|||
|
|||
JOINING OF TEXT & DATE
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 |
#10
Posted to microsoft.public.excel.newusers
|
|||
|
|||
JOINING OF TEXT & DATE
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 |
#11
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
#12
Posted to microsoft.public.excel.newusers
|
|||
|
|||
JOINING OF TEXT & DATE
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 "Gord Dibben" <gorddibbATshawDOTca wrote in message ... 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 |
#13
Posted to microsoft.public.excel.newusers
|
|||
|
|||
JOINING OF TEXT & DATE
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 InsertModule. 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 On Fri, 6 Oct 2006 07:51:15 -0400, "SJ" wrote: 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 "Gord Dibben" <gorddibbATshawDOTca wrote in message .. . 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
About Text to Date | Excel Discussion (Misc queries) | |||
turn off convert text to date (i.e. 4-9 to April 9, 2006) | Excel Worksheet Functions | |||
How do I grab the date from a text string? | Excel Worksheet Functions | |||
Macro to convert text to date | Excel Worksheet Functions | |||
EXTRACT TEXT FROM A DATE | Excel Worksheet Functions |