Combining fields in excel

[Tom Woolford]

I have several spreadsheets that have two separate fields for month and year. I would like to combine them into a single field (07/2008). I'm pretty good with excel, but this one has me stumped. Any ideas?

 

[Terrel L. Shields]

have two separate fields for month and year

since a date is recognized as a single number beginning Jan. 1, 1900 or some such, I doubt you can successfully merge the two fields.

 

[Howard Klahr]

In order to combine the two fields into one assuming the data is in column A (Month) and Column B (Year) enter the following formula in Column C:
+"'"&a1&"/"&b1

the formula starts off with a plus sign to signify a formula, the next character is a double quote, then a single quote and another double qoute. This lets excel know the result is to be text rather than numbers. The ampersand is the operator used to combine text a1 is the cell reference for the first field. The backslash surrounded by double quotes seperates the month from the year. b1 is the cell reference for the second field. Remember the result will be a text field not a date and therefore you can't perform calculations on the result.

Hope this helps.

 

[deturner]

Howard, I've never seen the + symbol work and it can be formatted to do calculations on.

Here is the process:
Assuming your data is in column A and Column B, place this formula in Column C:
=A1&"/"&B1 (don't know where the extra quotes Howard used came from)

Copy the formula down the remaining column (click the little plus sign in bottom right of cell and drag it to desired end of column)

Next, copy the new data, do a cut and paste special. Choose the option for "values only"

This should put the data in as a date.

(I just verified that it worked so let me know if you have problems)

DaveT in NC

 

[Metamorphic]

Here's another way of doing it.

Excel holds dates as a number. IIRC the number starts with 1 at 1/1/1900 and increments 1 per day. So for instance if you put in 1/1/2007, then convert the format of that from a date to a number it will return 39083. If you type in the number 39084 and convert that number to a date it will return the date 1/2/07.

So if you convert your year column to a number, write a simple forumla to convert your month column to a number (= "number of months" *30.5). Then added the two columns together and converted back to date format, you'd have a combined field.

 

[Tejus]

http://www.microsoft.com/office/com...5298&catlist=&dglist=&ptlist=&exp=&sloc=en-us

 

[Metamorphic]

very slick!

 

[Terrel L. Shields]

shoulda known who the real Guru is.