How to concatenate text & number cells and turn them into date format

[kterkuile]

Hi all,

I have two columns. Column A contains years, '2010' for example. Column B contains months, 'Jan' or 'Mar' for example. Neither column is in date format. I want to merge them to 'Jan 2010'. Now that's easy enough, but I also want to make the cell a date and i find that much more tricky. In fact I'm lost. Is there anyone who could help me please?

Take care,

Karel

 

[Jonmo1]

Try

=(B1&A1)+0

 

[kterkuile]

=(B1&A1)+0

Results in #VALUE. Shame; i liked it. Thanks for your trouble though!

Take care,

Karel

 

[kterkuile]

Sorry, it DOES work; '+' is not hte same as '&'. Silly me. Thanks so much!

Take care,

Karel

 

[kterkuile]

Final thought: why does it work?

Take care & thanks so much again!

Karel ter Kuile

 

[Jonmo1]

Final thought: why does it work?

Take care & thanks so much again!

Karel ter Kuile

Well, type this in a cell

Jan2010

What happens? Excel converts it to a date of Jan 1 2010.

here's what happens,
Unless you specifically format your cell as TEXT, Excel will always take your entry, and try to convert it to a number.

And dates are Numbers.
Jan 1 1900 = 1
Jan 2 1900 = 2
Jan 3 1900 = 3
Etc.
Mar 15 2010 = 40252

Excel has several Acceptable Date formats, and it just goes through them to see if what you entered matches an accepted Date format.
If it does, then it enters it as a date.


No just plain old
=B1&A1
That returns a TEXT string of "Jan2010"
The & symbol is a concatenation function, and specifically returns TEXT
So that alone, is specified as text, so Excel Does not try to convert it to a number.
By putting the +0 on it
=(B1&A1)+0
That forces excel to convert it to a number/date.

Hope that helps..

 

[kterkuile]

Well, that is so cunning you could put a tail on it and call it a weasel (to quote my favorite comedy character Blackadder). Thanks for the lesson in Excellence.

Take care,

Karel