Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Combining Date and String into 1 cell
I am trying to concatenate a string and a date into one cell.
However when I do so the date convert to its numerical value. I have them with a LF in a word wraped cell. =INDIRECT("'"&$A10&"'!e24")&" days"&CHAR(10)&INDIRECT("'"&$A10&"'! b24") I want it to show 7 days 10/20/08 but it is showing 7 days 39741 Can this be done? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Combining Date and String into 1 cell
Try this:
=INDIRECT("'"&$A10&"'!e24")&" days"&CHAR(10)&TEXT(INDIRECT("'"&$A10&"'!b24"),"mm/dd/yy") -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "gtslabs" wrote in message ... I am trying to concatenate a string and a date into one cell. However when I do so the date convert to its numerical value. I have them with a LF in a word wraped cell. =INDIRECT("'"&$A10&"'!e24")&" days"&CHAR(10)&INDIRECT("'"&$A10&"'! b24") I want it to show 7 days 10/20/08 but it is showing 7 days 39741 Can this be done? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Combining Date and String into 1 cell
On Tue, 21 Oct 2008 18:15:48 -0700 (PDT), gtslabs wrote:
I am trying to concatenate a string and a date into one cell. However when I do so the date convert to its numerical value. I have them with a LF in a word wraped cell. =INDIRECT("'"&$A10&"'!e24")&" days"&CHAR(10)&INDIRECT("'"&$A10&"'! b24") I want it to show 7 days 10/20/08 but it is showing 7 days 39741 Can this be done? You need to nest within the TEXT function to show the date the way you wish. e.g.: =TEXT(39741,"mm/dd/yy") Or, if I understand your formula correctly: .... &TEXT(INDIRECT("'"&$A10&"'!b24"),"mm/dd/yy") --ron |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Combining Date and String into 1 cell
Hi,
I will just add one comment, from your post its really not clear if you want to see the date as 1/1/08 or 01/01/08. I am going to assume the first, in which case change the TEXT format code to read: TEXT(INDIRECT("'"&$A10&"'!b24"),"m/d/yy") the date codes are as follows: m single digit month mm two digit month mmm three letter abreviation of the month's name mmmm full spelling of the month's name mmmmm single letter abbreviation of the month letter y or yy two digit year yyy or yyyy four digit year d single digit day of the month dd two digit day of the month ddd three letter abbreviation of the day of the week dddd full spelling of the day of the week -- Thanks, Shane Devenshire "gtslabs" wrote: I am trying to concatenate a string and a date into one cell. However when I do so the date convert to its numerical value. I have them with a LF in a word wraped cell. =INDIRECT("'"&$A10&"'!e24")&" days"&CHAR(10)&INDIRECT("'"&$A10&"'! b24") I want it to show 7 days 10/20/08 but it is showing 7 days 39741 Can this be done? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
isolate date from a text string into another cell | Excel Discussion (Misc queries) | |||
Combining date and time into one cell | Excel Discussion (Misc queries) | |||
Combining date to one cell with uniform results | Excel Discussion (Misc queries) | |||
Combining a string of text | Excel Discussion (Misc queries) | |||
combining text and numbers in a string | Excel Worksheet Functions |