asked on

Excel convert date to new layout from existing date entry

I download a weekly spreadsheet that has the date format at 09/01/2000, I need to change that date format to 2000-9-1. I have not found many articles that I can follow that will show how to change an existing date, everything so far is about formatting the today date.

Can anyone offer any help on how I would take the date format I am given and change to a new non standard format in Excel?

Current date format is 09/01/2000
Wanted format is 2000-9-1

ASKER CERTIFIED SOLUTION

dsacker

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

dsacker

If you prefer to completely change the actual data in that date column to that format:

Select an extra column to the right of all your columns. For example, say your date column is Column A, row 2. Column Z, row 2 (cell Z2), enter: =TEXT(A2, "yyyy-m-d")

Copy that cell all the way down to your last row.

Then, if you wish, simply copy those values back into your original date column.

A lot more steps, but that actually replaces the data with the format you're looking for. Still, the option in my previous post is easier, I would think.

James Elliott

dascker has provided the only two solutions that I know of, but a consequence of the second option is that it will no longer be considered a 'date' by excel.

This may or may not impact your future use of the data.

chrispre

ASKER

This was just what I needed. Thank you!