Change way date is displayed in cell....

I have a column ("general" format) in which dates are entered as 1992.03.04. Is there a way to convert all these dates to this format: 03/04/1992? Thanks!

Howard

Just to be clear, does that mean 4th March 1992, or 3rd April 1992.

Are you looking for a one-off approach (using Find/Replace or Text-to-columns), or a formula that can be applied after data entry (or even a macro to change the data in situ)?

Pete

Thanks for the quick response. It means March 4, 1992. I need to change the date format on a fairly small spreadsheet (about 300 rows), and there will be no more data gathering, so a one-off solution would be fine.

Or, as you phrased it, 4th March 1992. Yhe original format is yyyy.mm.dd, and I want mm/dd/yyyy. Thanks again!

Here's another one:

=--SUBSTITUTE(RIGHT(A23,5)&"/"&LEFT(A23,4),".","/")

Format the cell appropriately, then copy down.

Hope this helps.

Pete

Thanks. I am a very limited user of excel, so I need a little more explanation on how to use this. I assume that I make a new column to the right of the column with the original dates, and then I enter the formula in each cell of that column? If so, how do the references work? I assume that "A16" is a reference, but I don't know what the numbers after the commas are for. Many thanks!

Sorry, I was using A16 as a test cell in my spreadsheet as I don't know which column/row your dates are located (I've since updated the formula to reference A1). So yes, you would input this formula in a column adjacent to the current date column and reference the cell on the same row. You can then copy down the formula to apply to all other rows. The date formula I am using requires three parameters (year, month, day) so all I am doing is specifiy the locations of those paramaters from your original format. If you want to post your spreadsheet, I can update and re-post.

Now I have puzzled it out a little. I realize I have to change the source cell reference from A16 to the cell I want to change, but I still do not understand how the numbers following the cell reference work, i.e., what the numbers 6 and 2 mean in MID(A1,6,2). When I change the cell reference I get a proper format change, but no leading zeros, as so: 1992.03.14 becomes 3/14/1992, but I'd like to have 03/14/1992. Thanks!

By changing the format to date, you can select the way you want your date formatted. Unforunately there isn't a choice for leading zeros with a 4 digit year, although there is one with a 2 digit year.

Here is an alternative:
=MID(A1,6,2)&"/"&RIGHT(A1,2)&"/"&LEFT(A1,4)

Format as general.

Yes, that works. Thank you.