Dates converting to MS serial number
 

Scenario:
A1 value is "3/31/08"
In column C15, I type the formula, '="For the month ending "&A1'.

Problem:
It used to return, 'For the month ending 3/31/08'. Now it returns 'For the
month ending 39538'. I've tried changing the formatting and does not work.
I tried other formulas for the date but still returns the MS serial number.

Any help will be appreciated.

Marco

Dates converting to MS serial number
 

There is nothing wrong with that. Excel is doing as requested. A1 is the
number 39538. If you format that to display as a date, like m/dd/yy you will
see 03/31/08, but it's still a number so ="For the month ending "&A1 will
return "For the month ending 39538".
If you want to see: For the month ending 3/31/08 use: ="For the month
ending " & TEXT(A1,"m/dd/yy").

Tyro

"Marco" wrote in message
...
Scenario:
A1 value is "3/31/08"
In column C15, I type the formula, '="For the month ending "&A1'.

Problem:
It used to return, 'For the month ending 3/31/08'. Now it returns 'For
the
month ending 39538'. I've tried changing the formatting and does not
work.
I tried other formulas for the date but still returns the MS serial
number.

Any help will be appreciated.

Marco

Dates converting to MS serial number
 

That worked. Thank you.

"Tyro" wrote:

There is nothing wrong with that. Excel is doing as requested. A1 is the
number 39538. If you format that to display as a date, like m/dd/yy you will
see 03/31/08, but it's still a number so ="For the month ending "&A1 will
return "For the month ending 39538".
If you want to see: For the month ending 3/31/08 use: ="For the month
ending " & TEXT(A1,"m/dd/yy").

Tyro

"Marco" wrote in message
...
Scenario:
A1 value is "3/31/08"
In column C15, I type the formula, '="For the month ending "&A1'.

Problem:
It used to return, 'For the month ending 3/31/08'. Now it returns 'For
the
month ending 39538'. I've tried changing the formatting and does not
work.
I tried other formulas for the date but still returns the MS serial
number.

Any help will be appreciated.

Marco

Dates converting to MS serial number
 

Try it like this:

=IF(COUNT(A1),"For the month ending "&TEXT(A1,"m/d/yy"),"")


--
Biff
Microsoft Excel MVP


"Marco" wrote in message
...
Scenario:
A1 value is "3/31/08"
In column C15, I type the formula, '="For the month ending "&A1'.

Problem:
It used to return, 'For the month ending 3/31/08'. Now it returns 'For
the
month ending 39538'. I've tried changing the formatting and does not
work.
I tried other formulas for the date but still returns the MS serial
number.

Any help will be appreciated.

Marco