& in formula changing date format to text ?

[Steve]

I have this formula in a cell
=D12+1&" -> " &D12+5
It produces this : 39528 -> 39532
The cell format in the cell shows as m/dd/yy
D12 = 3/20/08
I would like it to produce
3/21/08 -> 3/25/08

If I lose everyting in the cell except the D12+1, the result is the correct
date.
I believe the & is causing the format to change to the date#'s instead of
the date.

How can I get the cell to produce 3/21/08 -> 3/25/08 ?

Thanks,

Steve

 

[PCLIVE]

Since your cell can't be formatted as a date due to the additional data or
text that is being produced, then the output will be automatically displayed
as Text. You could get your desired result with:

=MONTH(D12)&"/"&DAY(D12)+1&"/"&YEAR(D12)&" ->
"&MONTH(D12)&"/"&DAY(D12)+5&"/"&YEAR(D12)

HTH,
Paul

 

[T. Valko]

I believe the & is causing the format to change to the date#'s instead of

the date.

You're concatenating TEXT into the formula so the result is a TEXT string
which ignores number formatting.

Try it like this:

=TEXT(D12+1,"m/dd/yy")&" -> "&TEXT(D12+5,"m/dd/yy")

 

[Steve]

Thanks guys. Both worked great.

Much appreciated,

Steve

 

[T. Valko]

You're welcome!

--
Biff
Microsoft Excel MVP

Thanks guys. Both worked great.

Much appreciated,

Steve

 

[iashorty]

Thanks Paul. I was able to use this formula to compare two fields brought in
with two different formattings.