concatenate 2

[CCNProjects]

Hi.

I have two cells containing dates. in cell A, I have
jan-19, and in cell B, I have February 2, 2004.

What i would like to get is jan 19-February 2, 2004.

the date in cell A is stored as a date, but in cell B as text.

I tried to use this formula: =TEXT(B8,"DD MMM YY") &"-"&C8
I got this result: 01 Jan 19- February 2, 2004

How do I get rid of the 01 at the beginning?
Thanks.

 

[SkipVought]

Hi,
[tt]
=TEXT(B8,"MMM dd")&"-"&TEXT(C8,"MMM dd, yyyy")
[/tt]


Skip,

_[red][/red]

 

[LNBruno]

just get rid of the YY in your formula?


< M!ke >
Acupuncture Development: a jab well done.

 

[CCNProjects]

thanks.It worked.

 

[CCNProjects]

well, it did not work!
I get jan 01-February 2, 2004

the desirable outcome is jan 19-February 2, 2004

Any hint?

Thanks

 

[LNBruno]

How is the date in A8 formatted?

I've got this:

A8      B8                      C8
Jan-19	February 2, 2004	19 Jan-February 2, 2004

where A8 = 1/19/2007
B8 = February 2, 2004
and C8 = =TEXT(B8,"DD MMM") &"-"&C8


< M!ke >
Acupuncture Development: a jab well done.

 

[SkipVought]

Is the value in C8 a REAL DATE?

Format the cell as GENERAL. It should change to a number.

Skip,

_[red][/red]

 

[SkipVought]

faq68-5827.

Skip,

_[red][/red]

 

[CCNProjects]

thanks for your feedback.

the cell B8 displays jan-19, but 01/01/2019 is actually stored! When converted to general format, I get 43466.

So, the display is correct but the date itself is not!

It is more complicated than I thought.

 

[LNBruno]

So what you're getting is the month and year, not the month and day...

< M!ke >
Acupuncture Development: a jab well done.

 

[yogia]

Hi CNNProjects:

Let us have a look at ...

  |A           |B               |C
--|------------|----------------|---------------------                
 1|Jan-19	   February 2,2004  Jan-19-February 2,2004
 219-Jan	    February 2,2004  Jan-19-February 2,2004

formula in cell C2 is ...
=TEXT(A1,"mmm-dd")&"-"&TEXT(B1,"mmmm d,yyyy")


Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[CCNProjects]

strange! with your formula: =TEXT(A1,"mmm-dd")&"-"&TEXT(B1,"mmm dd,yyyy"), I get
Jan-01-feb 02,004

I tried every date format.

thanks.

 

[SkipVought]

Did you try CHANGING the format on B1 to GENERAL, just to OBSERVE what happens?

If the format does not change to a NUMBER, then you do not have a REAL DATE!

Tell us what happens, please.

Skip,

_[red][/red]

 

[LNBruno]

the date in cell A is stored as a date, but in cell B as text

...so only the value in cell A needs to be converted to text for text concatenation...n'cest pas?

< M!ke >
Acupuncture Development: a jab well done.

 

[CCNProjects]

thanks SkipVought.
B1 is stored as text.

 

[SkipVought]

COnvert TEXT to DATE using MID & DATE functions. The TEXT formatting will work.

Skip,

_[red][/red]

 

[LNBruno]

I'm still wondering why I got what you were asking for in my post of 13:27, but you still are having trouble...

What's different?

< M!ke >
Acupuncture Development: a jab well done.