& in formula changing date format to text ?
 

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

& in formula changing date format to text ?
 

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


--

"Steve" wrote in message
...
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

& in formula changing date format to text ?
 

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")


--
Biff
Microsoft Excel MVP


"Steve" wrote in message
...
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

& in formula changing date format to text ?
 

Thanks guys. Both worked great.

Much appreciated,

Steve

"T. Valko" wrote:

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")


--
Biff
Microsoft Excel MVP


"Steve" wrote in message
...
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

& in formula changing date format to text ?
 

You're welcome!

--
Biff
Microsoft Excel MVP


"Steve" wrote in message
...
Thanks guys. Both worked great.

Much appreciated,

Steve

"T. Valko" wrote:

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")


--
Biff
Microsoft Excel MVP


"Steve" wrote in message
...
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

& in formula changing date format to text ?
 

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


"PCLIVE" wrote:

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


--

"Steve" wrote in message
...
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