Combining Date and String into 1 cell

[gtslabs]

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?

 

[RagDyeR]

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!
=====================================================


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?

 

[Ron Rosenfeld]

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

 

[ShaneDevenshire]

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