different formatting, same cell

M

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?
 
R

Ron Coderre

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
 
G

Gord Dibben

Matt

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

Your choice of date format.


Gord Dibben MS Excel MVP
 
M

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
Click to expand...



Exactly! Thanks!
 
E

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.
 
R

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.
 
E

Earl Kiosterud

RD,

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

"Completed" mm/dd/yy;;

--
Earl Kiosterud
www.smokeylake.com
-----------------------------------------------------------------------
Ragdyer said:
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 !
---------------------------------------------------------------------------
Earl Kiosterud said:
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.
Click to expand...
Click to expand...
 
R

Ragdyer

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

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
Earl Kiosterud said:
RD,

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

"Completed" mm/dd/yy;;

--
Earl Kiosterud
www.smokeylake.com
-----------------------------------------------------------------------
Ragdyer said:
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

--------------------------------------------------------------------------
Click to expand...
-
Please keep all correspondence within the NewsGroup, so all may benefit
Click to expand...
!
--------------------------------------------------------------------------
Click to expand...
-
same
formatting for
Click to expand...
Click to expand...
 
Top