Dates converting to MS serial number

[Marco]

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

 

[Tyro]

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]

That worked. Thank you.

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

 

[T. Valko]

Try it like this:

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