Converting e.g. 28-07-2004 to recognised date format

Hello

I've got a column full dates in a non standard format, like below. How
can I convert these to an excel recognised format?

28-07-2004_
30-07-2003_
31-07-2002_
01-08-2001_
31-07-2000_
02-08-1999_
03-08-1998_
04-08-1997_
29-07-1996_
17-07-1995_

Thanks,

Bertie


---
Message posted from http://www.ExcelForum.com/

Hi Bertie!

Here's one way:

Use a helper column and this formula:

=DATE(MID(A1,7,4),MID(A1,4,2),LEFT(A1,2))

Format to the date style that you want. then you could
delete the orginal list if you so desire.

Biff

-----Original Message-----
Hello

I've got a column full dates in a non standard format,
like below. How
can I convert these to an excel recognised format?

28-07-2004_
30-07-2003_
31-07-2002_
01-08-2001_
31-07-2000_
02-08-1999_
03-08-1998_
04-08-1997_
29-07-1996_
17-07-1995_

Thanks,

Bertie


---
Message posted from http://www.ExcelForum.com/

.

You can use a formula like

=DATE(MID(A1,7,4),MID(A1,4,2),LEFT(A1,2))


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"claytorm " wrote in
message ...
Hello

I've got a column full dates in a non standard format, like
below. How
can I convert these to an excel recognised format?

28-07-2004_
30-07-2003_
31-07-2002_
01-08-2001_
31-07-2000_
02-08-1999_
03-08-1998_
04-08-1997_
29-07-1996_
17-07-1995_

Thanks,

Bertie


---
Message posted from http://www.ExcelForum.com/

On Fri, 20 Aug 2004 12:14:54 -0500, claytorm
wrote:

I've got a column full dates in a non standard format, like below. How
can I convert these to an excel recognised format?

28-07-2004_
30-07-2003_
31-07-2002_

=DATE(MID(A1,7,4),MID(A1,4,2),LEFT(A1,2))

will convert your data to an Excel date number.

Enter it into a blank column. Adjust A1 to reflect the proper location.
Copy/Drag down as needed. Format to taste.

Then do a copy/paste special values over the original data delete the helper
column.

If this is going to be a repetitive task, you could use a macro to convert the
data in place.


--ron

Ooops!

Don't delete that original list just yet!!!

If you want to delete it, first you must convert the
formulas to constants. Select the range of formulas then
do: CopyPaste SpecialValues.

Now you can safely delete the original list.

Here's an alternative method that does not require the use
of a helper column and formulas.

Use the Text To Columns feature in the Data menu.

Select the range of values.
Goto DataText to Columns
Select: Fixed width
Click Next
In the data preview section on the "ruler" click the hash
mark under the 10. This will insert a separator before the
last underscore in the string.
Click Next
In the column data format area, click Date and from the
dropdown select the format you want.
Click Finish.
Now you can just delete the column where the separated
underscore is.

Sounds "complicated" but it isn't and it takes only a few
seconds to accomplish.

Biff

-----Original Message-----
Hi Bertie!

Here's one way:

Use a helper column and this formula:

=DATE(MID(A1,7,4),MID(A1,4,2),LEFT(A1,2))

Format to the date style that you want. then you could
delete the orginal list if you so desire.

Biff

-----Original Message-----
Hello

I've got a column full dates in a non standard format,
like below. How
can I convert these to an excel recognised format?

28-07-2004_
30-07-2003_
31-07-2002_
01-08-2001_
31-07-2000_
02-08-1999_
03-08-1998_
04-08-1997_
29-07-1996_
17-07-1995_

Thanks,

Bertie


---
Message posted from http://www.ExcelForum.com/

.

.

Bertie

DataText to ColumnsNextNext "column data format"DateDMYFinish.

Gord Dibben Excel MVP

On Fri, 20 Aug 2004 12:14:54 -0500, claytorm
wrote:

Hello

I've got a column full dates in a non standard format, like below. How
can I convert these to an excel recognised format?

28-07-2004_
30-07-2003_
31-07-2002_
01-08-2001_
31-07-2000_
02-08-1999_
03-08-1998_
04-08-1997_
29-07-1996_
17-07-1995_

Thanks,

Bertie


---
Message posted from http://www.ExcelForum.com/

And divide that underscore character into another field and choose to skip that
one.

Gord Dibben wrote:

Bertie

DataText to ColumnsNextNext "column data format"DateDMYFinish.

Gord Dibben Excel MVP

On Fri, 20 Aug 2004 12:14:54 -0500, claytorm
wrote:

Hello

I've got a column full dates in a non standard format, like below. How
can I convert these to an excel recognised format?

28-07-2004_
30-07-2003_
31-07-2002_
01-08-2001_
31-07-2000_
02-08-1999_
03-08-1998_
04-08-1997_
29-07-1996_
17-07-1995_

Thanks,

Bertie


---
Message posted from http://www.ExcelForum.com/

--

Dave Peterson

Dave

When I copied the data including underscore and sent it through T to T it
changed the data to dates and deleted/ignored the underscore character
automagically.

Gord

On Fri, 20 Aug 2004 16:01:06 -0500, Dave Peterson wrote:

And divide that underscore character into another field and choose to skip that
one.

Gord Dibben wrote:

Bertie

DataText to ColumnsNextNext "column data format"DateDMYFinish.

Gord Dibben Excel MVP

On Fri, 20 Aug 2004 12:14:54 -0500, claytorm
wrote:

Hello

I've got a column full dates in a non standard format, like below. How
can I convert these to an excel recognised format?

28-07-2004_
30-07-2003_
31-07-2002_
01-08-2001_
31-07-2000_
02-08-1999_
03-08-1998_
04-08-1997_
29-07-1996_
17-07-1995_

Thanks,

Bertie


---
Message posted from http://www.ExcelForum.com/

Ouch.

I knew I should have tested. (I know you did!)

But still not a bad tip if there's characters that are less forgiving. (back
pedaling as fast as I can!)

Gord Dibben wrote:

Dave

When I copied the data including underscore and sent it through T to T it
changed the data to dates and deleted/ignored the underscore character
automagically.

Gord

On Fri, 20 Aug 2004 16:01:06 -0500, Dave Peterson wrote:

And divide that underscore character into another field and choose to skip that
one.

Gord Dibben wrote:

Bertie

DataText to ColumnsNextNext "column data format"DateDMYFinish.

Gord Dibben Excel MVP

On Fri, 20 Aug 2004 12:14:54 -0500, claytorm
wrote:

Hello

I've got a column full dates in a non standard format, like below. How
can I convert these to an excel recognised format?

28-07-2004_
30-07-2003_
31-07-2002_
01-08-2001_
31-07-2000_
02-08-1999_
03-08-1998_
04-08-1997_
29-07-1996_
17-07-1995_

Thanks,

Bertie


---
Message posted from http://www.ExcelForum.com/

--

Dave Peterson

On Fri, 20 Aug 2004 14:57:42 -0700, Gord Dibben gorddibbATshawDOTca wrote:

When I copied the data including underscore and sent it through T to T it
changed the data to dates and deleted/ignored the underscore character
automagically.

Interesting. It did not do that here (Excel 2002). As a matter of fact, even
with T to C and setting the underscore as a separator, only some of the entries
were changed.


--ron