Excel formatting help needed

July 22, 2009 11:24 AM

Question on Excel date format changes- please help hivemind, you are my only hope!

I have a bi-monthly report that I have to scrub and reformat. One of the biggest issues I have is changing the date format from 2009.12.07 to 12/07/2009

There are multiple columns with over 300 rows in each column and currently I am exporting the original date formats (2009.12.07) to a csv file opened in notepad, replacing . with a comma, reimporting it into excel, moving year over to the third column, saving it as a csv file and opening it in notepad againand replacing the space with a / to be reimported into the original excel file.

Workflow:

2009.12.07 in excel
to
2009,12,07 done in notepad using find and replace
2009 12 07 in 3 different cells in excel
12 07 2009 by moving the first cell to the 3rd cell in excel and open in notepad
12 07 2009 in notepad find and replace spaces with /
12/07/2009 in notepad and import back into original excel file

The workflow is ridiculous and my googlefu has failed me for VBA as well as conditional formatting.

posted by TheBones to education (10 comments total)

Changing the period to a slash, importing to Excel and formatting the column as a date doesn't work for you? What version of Excel?
posted by rheumy_the_dwarf at 11:31 AM on July 22

Try this:
1. Highlight your column of 2009.12.07 format dates (let's say it's column A)
2. Text to columns -> Delimited (check Other and put in a ".")
3. You should now have 3 columns, column A = year, column B = month, column C = day
4. Insert a 4th column (D) and enter the formula =DATE(year, month, day)
5. You can then format this formula cell to your desired date format.
posted by chalbe at 11:33 AM on July 22

Addendum: to be specific with the formula, if you were putting the formula in cell D1, it should be
=DATE(A1,B1,C1)
posted by chalbe at 11:36 AM on July 22

Assuming "2009.12.07" is in A1, you could use this formula and then copy/paste values:

=VALUE(MID(A1,6,2)&"/"&RIGHT(A1,2)&"/"&LEFT(A1,4))
posted by mullacc at 11:36 AM on July 22

Are the single digit dates formatted as 2009.1.1 or 2009.01.01?

If the latter, you can just make a new column with a value of =date(mid(A1,1,4),mid(A1,6,2),mid(A1,9,2)). This will be a nice Excel date, that you can then format using the typical Excel formatting.
posted by smackfu at 11:37 AM on July 22

Ridiculously easy! Thank you
posted by TheBones at 11:39 AM on July 22

Ahaha I just found out an even easier way to do this
1. Select your column of dates formatted 2009.12.07
2. Hit Ctrl+H to bring up the Replace function
3. In the Find what box, : .
In the Replace with box, put: /
This will replace all your . with / in the cells, and Excel will autoconvert this to a date format
posted by chalbe at 11:39 AM on July 22

If your dates are in A1 down to Axxxx, then in B1 put:

=DATE(MID(A1,1,4),MID(A1,6,2),MID(A1,9,2))

and then copy it down.

You can now manipulate the format of the date in column B as you wish.

Note - i'm assuming you will always have 2 digits for month and day (based on you using 07 for the day)

Oh and I'm also assuming 7 is the day and 12 the month.
posted by saintsguy at 11:40 AM on July 22

Now that you're there, if it's important that it's 12/07/2009 instead of 12/7/2009, you can go into formats and use a custom format of mm/dd/yyyy.
posted by brainmouse at 11:42 AM on July 22

Usually Excel can do a find and replace just as easily as Notepad can.

Also, if you have data in one cell that you want to spread out, use Data-> Text to Columns.
Tell it to use a period as the delimiter and you can change 2009.03.14 to 2009 | 03| 14 (in three different cells) very easily. Just make sure you have enough blank columns to the right of your data to fit the new data in.
posted by soelo at 2:14 PM on July 22