different formatting, same cell

[Matt]

Say the date that a certain event occurs (2/17/07) is in cell A1.
I would like to have cell B1 display: Completed 2/17/07
If I use the formula ="Completed "&A1, it displays: Completed 39130
Is there another way to do this?

 

[Guest]

To Excel, dates are just numbers, so you need to tell it to convert the
number (date) to text how to display that text.

Try something like this:
="Completed "&TEXT(A1,"MM/DD/YYYY")

Does that help?
***********
Regards,
Ron

XL2002, WinXP

 

[Gord Dibben]

Matt

="Completed " &TEXT(A1,"mmmm, d yyyy")

Your choice of date format.


Gord Dibben MS Excel MVP

 

[Matt]

To Excel, dates are just numbers, so you need to tell it to convert the
number (date) to text how to display that text.

Try something like this:
="Completed "&TEXT(A1,"MM/DD/YYYY")

Does that help?
***********
Regards,
Ron

XL2002, WinXP

Exactly! Thanks!

 

[Earl Kiosterud]

Matt,

You can make A1 say "Completed 2/17/07". With A1 selected, Format - Cells - Number tab -
Custom. In the box, put:

"Completed" mm/dd/yy

Include the quote marks. Or you can make B1 do that with =A1, and the same formatting for
B1.

 

[Ragdyer]

Trouble with that Earl, is that empty cells will display:
Completed 01/00/00

So, the
=A1
formula should become something like this:
=IF(A1>0,A1,"")

OR ... to retain the
=A1
formula, the custom format should be something like:

[>0]"Completed "mm/dd/yy;General;

With the last semi-colon included to eliminate 0's when empty cells are in
the first column.

 

[Earl Kiosterud]

RD,

Oops. You're right. Similar to your second solution, you could use

"Completed" mm/dd/yy;;

--
Earl Kiosterud
www.smokeylake.com
-----------------------------------------------------------------------

Trouble with that Earl, is that empty cells will display:
Completed 01/00/00

So, the
=A1
formula should become something like this:
=IF(A1>0,A1,"")

OR ... to retain the
=A1
formula, the custom format should be something like:

[>0]"Completed "mm/dd/yy;General;

With the last semi-colon included to eliminate 0's when empty cells are in
the first column.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

Matt,

You can make A1 say "Completed 2/17/07". With A1 selected, Format - Cells - Number tab -
Custom. In the box, put:

"Completed" mm/dd/yy

Include the quote marks. Or you can make B1 do that with =A1, and the same formatting for
B1.

 

[Ragdyer]

I like that one even better!<g>
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

RD,

Oops. You're right. Similar to your second solution, you could use

"Completed" mm/dd/yy;;

--
Earl Kiosterud
www.smokeylake.com
-----------------------------------------------------------------------

Trouble with that Earl, is that empty cells will display:
Completed 01/00/00

So, the
=A1
formula should become something like this:
=IF(A1>0,A1,"")

OR ... to retain the
=A1
formula, the custom format should be something like:

[>0]"Completed "mm/dd/yy;General;

With the last semi-colon included to eliminate 0's when empty cells are in
the first column.
--
Regards,

RD

--------------------------------------------------------------------------

-

Please keep all correspondence within the NewsGroup, so all may benefit

!

--------------------------------------------------------------------------

-

same
formatting for