When using & and a date-formatted cell in a formula I lose the for

[Jeff Stouse]

Hi all,

If I have a serial number (such as 39401) representing a date, and
formatting a date) which displays 11/15/2007 in A2, and I want to join that
with text in a cell A3 using the following formula:

="Start Date: "&A2

The result is:

Start date: 39401

Not:

Start date: 11/15/2007

So I understand that Excel loses the formatting of the number in the cell as
soon as I use the & to concantenate text to it, but I can't find a function
that will turn the value (39401) into a date when used in a formula...

Any ideas?

TIA,
Jeff

 

[Niek Otten]

Hi Jeff,

="Start Date: "&TEXT(A2,"mm/dd/yyyy")

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Hi all,
|
| If I have a serial number (such as 39401) representing a date, and
| formatting a date) which displays 11/15/2007 in A2, and I want to join that
| with text in a cell A3 using the following formula:
|
| ="Start Date: "&A2
|
| The result is:
|
| Start date: 39401
|
| Not:
|
| Start date: 11/15/2007
|
| So I understand that Excel loses the formatting of the number in the cell as
| soon as I use the & to concantenate text to it, but I can't find a function
| that will turn the value (39401) into a date when used in a formula...
|
| Any ideas?
|
| TIA,
| Jeff
|

 

[Pete_UK]

Try it this way:

="Start Date: "&TEXT(A2,"mm/dd/yyyy")

Hope this helps.

Pete