Separating dates into year, month and day componrents

5 pts.
Tags:
Date conversion
Excel 2002
Excel 2007
I have an excel file of some 80,000+ records, one of whose columns - C- is in date - YYYY/MM/DD form. I want to split off the date into separate columns of years and months. I've managed to split off the years by using the expression LEFT (C2,4) where C is the label of the column containing the YYYY/MM/DD date, but I'm unsure how to achieve the same result with the months, especially since only a small proportion of the month data (months 10, 11 and 12) has been recorded as having two characters.
1

Answer Wiki

Thanks. We'll let you know when a new response is added.

To do this, use Text to Columns. Select the column where you have the dates, go to Data, Text to Columns, select Delimited in Step 1, in Step 2 make sure that the “other” checkbox is activated and put a “/” in the box to the right of the other checkbox. Finally, in step 3 select a Destination cell (It defaults to the current cell). Hit Finish and that’s it.

—————–

If you format column C as a date (you can use a custom format of yyyy/mm/dd if you want) then you can use the formulas =YEAR(Cn), =MONTH(Cn) and =DAY(Cn) to give you the separate values.

Jonathan

—————-

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • SbElectric
    Jonathan provided the correct answer. This is just to elaborate on his comments (if you do not have Excel 2007). As Jonathan has suggested, need to Format the cells with custom format. Highlight the column & then right click, Choose Format Cells. Select Custom (under category) and type yyyy/mm/dd (under Type). Then OK. This will ensure that the column is formatted properly. Assuming columns D, E, and F will contain Yr, Month, and Day. On D2 Type =Year(C2); on E2 type =Month(C2): on F2 type =Day(C2) Hopefully you will see the correct data now. If Looks OK you can now just stretch the bottom right “handle” on D2 and drag it to the end for Year, Do similar for Month & Day. Important caveat – make a back up of the Excel & may be experiment on a small spreadsheet to fully understand the concept before attempting on the main data. Good luck.
    2,540 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.

Following

Share this item with your network: