Joining text formatted as a date???

[Dan B]

I'm having a problem joining text that is fomatted as a date. Here are the
contents of the cells I'm trying to join:
D19: 01/01/04
E19: to
F19: 01/01/05
G19: 12:01 A.M.

The formula I'm using to join those all together:
=D19&" "&E19&" "&F19&" "&G19
Which should give me: 01/01/04 to 01/01/05 12:01 A.M.

The result I get is: 37987 to 38353 12:01 A.M.
D19 and F19 are formatted as dates. I have tried various date formats on
D19, F19 and my formula. Nothing seems to come out right. What am I doing
wrong?

Thanks,
Dan

 

[Jane Graves]

You're getting the serial numerical value of the dates.
You have to make them text first. Try:
text(D19,"mm/dd/yy")&" "&E19&" "&text(F19,"mm/dd/yy")
&" "&G19

As a side note, if the word in column E is always "to",
you don't need the column. Change the above formula to:
text(D19,"mm/dd/yy")&" to "&text(F19,"mm/dd/yy")&" "&G19

Jane

 

[Felipe]

Dan,

Dates are managed as numbers by excel, no matter what
format they have. For more information on this check excel
help for 'How Microsoft Excel handles dates'

You can use DAY, MONTH and YEAR functions for your problem:

=+DAY(D19)&"/"&MONTH(D19)&"/"&YEAR(D19)&" "&F19&" "&DAY
(E19)&"/"&MONTH(E19)&"/"&YEAR(E19)&" "&G19

Regards,
Felipe

 

[Dan B]

Makes sense....thanks!!