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?

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


"Matt" wrote:

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

Matt

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

Your choice of date format.


Gord Dibben MS Excel MVP


On 17 Feb 2007 14:42:57 -0800, "Matt" <(E-Mail Removed)> wrote:

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

On Feb 17, 4:53 pm, Ron Coderre <ronREMOVETHIScode...@bigfoot.com>
wrote:
> 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
>
> "Matt" wrote:
> > 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?



Exactly! Thanks!

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.

--
Earl Kiosterud
www.smokeylake.com
-----------------------------------------------------------------------
"Matt" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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?
>

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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.
>
> --
> Earl Kiosterud
> www.smokeylake.com
> -----------------------------------------------------------------------
> "Matt" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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?
> >
>
>

RD,

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

"Completed" mm/dd/yy;;

--
Earl Kiosterud
www.smokeylake.com
-----------------------------------------------------------------------
"Ragdyer" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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.
>>
>> --
>> Earl Kiosterud
>> www.smokeylake.com
>> -----------------------------------------------------------------------
>> "Matt" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>> > 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?
>> >
>>
>>
>

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

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Earl Kiosterud" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> RD,
>
> Oops. You're right. Similar to your second solution, you could use
>
> "Completed" mm/dd/yy;;
>
> --
> Earl Kiosterud
> www.smokeylake.com
> -----------------------------------------------------------------------
> "Ragdyer" <(E-Mail Removed)> wrote in message
> news:%(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
> > news:(E-Mail Removed)...
> >> 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.
> >>
> >> --
> >> Earl Kiosterud
> >> www.smokeylake.com
> >> -----------------------------------------------------------------------
> >> "Matt" <(E-Mail Removed)> wrote in message
> >> news:(E-Mail Removed)...
> >> > 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?
> >> >
> >>
> >>
> >
>
>