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

[claytorm]

Hello

I've got a column full dates in a non standard format, like below. Ho
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,

Berti

 

[Biff]

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

 

[Chip Pearson]

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

 

[Ron Rosenfeld]

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

 

[Biff]

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: Copy>Paste Special>Values.

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 Data>Text 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

 

[Gord Dibben]

Bertie

Data>Text to Columns>Next>Next "column data format">Date>DMY>Finish.

Gord Dibben Excel MVP

 

[Dave Peterson]

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

 

[Gord Dibben]

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

 

[Dave Peterson]

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!)

 

[Ron Rosenfeld]

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

 

[Dave Peterson]

I used xl2002 when I tested and it worked ok for me.

 

[claytorm]

Thanks for your replies guys. I finally used Gord Dibben's solutio
(Data>Text to Columns>Next>Next "column data format">Date>DMY>Finish.
which worked well.

On a related theme...

I still have a problem with some numbers imported as text from the sam
source. The cells are formatted General, and have 19 spaces after th
last digit in each. I have tried several solutions to clean the dat
and convert it to a number with no success.

Some of the solutions I've tried already:

I have tried CLEAN, then using VALUE on the result to convert to
number, but this returns #VALUE!

I have also tried find and replace on the spaces.

I have also tried various macros recommended elsewhere.


My thinking is that the spaces are in some way not normal. I attach
sheet with example data.

Any comments appreciated

Attachment filename: egdata.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=65594

 

[Gord Dibben]

clay

This worked for me on your sheet.

Sub NumFormat()
Dim cel As Range
On Error GoTo endit
For Each cel In Selection
cel.Value = cel.Value * 1
cel.numberformat = "0.00"
Next cel
Exit Sub
endit:
MsgBox "No cells found!"
End Sub

Gord Dibben Excel MVP