Joining dates and text in formula
 

Hi,

I have a simple formula which is joins 2 dates. To demonstrate

Cell D17 = 1/1/07
Cell E17 = 8/1/07

When I join them using the formula "=D17&" to "&E17" the date formats
aren't retained and I get the answer "39083 to 39090". The cell has
date format set.

How do I get the cell to return "1st Jan 07 to 8th Jan 07"?

Thanks

Joining dates and text in formula
 

Use the text function:

=TEXT(D17,"d mmm yy") & " to " & TEXT(E17,"d mmm yy")


but notice no "st" or "rd", etc.
--
Gary's Student
gsnu200703


"RJS" wrote:

Hi,

I have a simple formula which is joins 2 dates. To demonstrate

Cell D17 = 1/1/07
Cell E17 = 8/1/07

When I join them using the formula "=D17&" to "&E17" the date formats
aren't retained and I get the answer "39083 to 39090". The cell has
date format set.

How do I get the cell to return "1st Jan 07 to 8th Jan 07"?

Thanks

Joining dates and text in formula
 

Thanks v. much.


Gary''s Student wrote:

Use the text function:

=TEXT(D17,"d mmm yy") & " to " & TEXT(E17,"d mmm yy")


but notice no "st" or "rd", etc.
--
Gary's Student
gsnu200703


"RJS" wrote:

Hi,

I have a simple formula which is joins 2 dates. To demonstrate

Cell D17 = 1/1/07
Cell E17 = 8/1/07

When I join them using the formula "=D17&" to "&E17" the date formats
aren't retained and I get the answer "39083 to 39090". The cell has
date format set.

How do I get the cell to return "1st Jan 07 to 8th Jan 07"?

Thanks

Joining dates and text in formula
 

Just a late note here.

If you did want the "st" or "rd" eyc. you could employ a user defined function
to provide those.

Post back if interested.


Gord Dibben MS Excel MVP

On 1 Feb 2007 01:48:36 -0800, "RJS" wrote:

Thanks v. much.


Gary''s Student wrote:

Use the text function:

=TEXT(D17,"d mmm yy") & " to " & TEXT(E17,"d mmm yy")


but notice no "st" or "rd", etc.
--
Gary's Student
gsnu200703


"RJS" wrote:

Hi,

I have a simple formula which is joins 2 dates. To demonstrate

Cell D17 = 1/1/07
Cell E17 = 8/1/07

When I join them using the formula "=D17&" to "&E17" the date formats
aren't retained and I get the answer "39083 to 39090". The cell has
date format set.

How do I get the cell to return "1st Jan 07 to 8th Jan 07"?

Thanks