Converting 8 digit numbers into dates

A

andy.cotgreave

Hi,
I have a spreadsheet with 1800 dates in one column. However, Excel
doesn't recognise them as a date. Why? Because they are in the
following format:

20050512

Which I know is yyyymmdd.

However, I cannot work out a way to convert these, in a macro, to
something that Excel recognises as a UK date (dd/mm/yyyy).

I've writtne a macro that rearranges the numbers, but the new data
doesn't recognise amiguous US/UK dates properly (ie is 20050512 the
fifth of December or the twelfth of May?):

Sub ChangeDate()
For Each c In Range("A2:A1801").Cells
' For each cell, re-arrange the numbers - last 2 digits, middle 2
and first 4, putting in a "/" between them
c.Value = Right(c.Value, 2) & "/" & Mid(c.Value, 5, 2) & "/" &
Left(c.Value, 4)
Next

Any help much appreciated....
 
B

Bob Phillips

Sub ChangeDate()
Dim c As Range
For Each c In Range("A2:A1801").Cells
c.Value = DateSerial(Left(c.Value, 4), Mid(c.Value, 5, 2),
Right(c.Value, 2))
Next

End Sub


--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)
 
P

Pete_UK

Why not just use a formula? If your dates start in A1, enter this in
B1:

=VALUE(RIGHT(A1,2)&"/"&MID(A1,5,2)&"/"&LEFT(A1,4))

Format the cell as a date and copy down. You could fix the values in
column B and then delete the originals in column A.

Hope this helps.

Pete
 
A

andy.cotgreave

I thought about that, but it's a task that will be repeated, and I
don't want to go through the burden of putting extra columns in and
then deleting them every time I need to do this conversion.

Thanks

Andy
 
D

Dave Peterson

Another option would be to select the single column range and do

data|text to columns
fixed width (but no separators)
choose ymd for the date format
and plop it back from where you got it.

You could record a macro when you do this if you needed code.
 
G

Gary''s Student

Try:

Sub fixup()
Dim s As String, s2 As String
For Each r In Range("A2:A1801").Cells
s = r.Value
s2 = Mid(s, 5, 2) & "/" & Mid(s, 7, 2) & "/" & Mid(s, 1, 4)
r.Clear
r.Value = s2
r.NumberFormat = "dd/mm/yyyy"
Next
End Sub
 
D

Dana DeLouis

In the US, this works for me. However, I'm not sure about UK date system.
The '5' you see is the 3rd item down in the "Date" pull-down in the "Column
data format". (ie YMD).

[A1:A100].TextToColumns _
Destination:=Range("A1"), _
DataType:=xlDelimited, _
FieldInfo:=Array(1, 5)

Again, don't know if it would work for you.
 
L

Lori

You could also execute this (from the immediate window):

[a2:a1801]=[Text(a2:a1801,"0000-00-00")]
 
R

Roger Govier

Hi Dana

FYI this works perfectly on the UK Date system
converting 20050512 to 12/05/2005 (12th May)

Changing Array(1,5) to Array(1,8) will also quite happily handle
YYYYDDMM format
converting 20050512 to 05/12/2005 (5th December)

--
Regards

Roger Govier


Dana DeLouis said:
In the US, this works for me. However, I'm not sure about UK date
system.
The '5' you see is the 3rd item down in the "Date" pull-down in the
"Column data format". (ie YMD).

[A1:A100].TextToColumns _
Destination:=Range("A1"), _
DataType:=xlDelimited, _
FieldInfo:=Array(1, 5)

Again, don't know if it would work for you.
--
Dana DeLouis
Windows XP & Office 2003


Hi,
I have a spreadsheet with 1800 dates in one column. However, Excel
doesn't recognise them as a date. Why? Because they are in the
following format:

20050512

Which I know is yyyymmdd.

However, I cannot work out a way to convert these, in a macro, to
something that Excel recognises as a UK date (dd/mm/yyyy).

I've writtne a macro that rearranges the numbers, but the new data
doesn't recognise amiguous US/UK dates properly (ie is 20050512 the
fifth of December or the twelfth of May?):

Sub ChangeDate()
For Each c In Range("A2:A1801").Cells
' For each cell, re-arrange the numbers - last 2 digits, middle 2
and first 4, putting in a "/" between them
c.Value = Right(c.Value, 2) & "/" & Mid(c.Value, 5, 2) & "/" &
Left(c.Value, 4)
Next

Any help much appreciated....
Click to expand...
Click to expand...
 
A

andy.cotgreave

That's a really clever solution.

"Text to columns" wasn't originally intended to be used for this kind
of conversion, was it? I've not used it before.

Andy
 
D

Dave Peterson

I'm not sure what original intent was, but excel is pretty smart and will handle
dates pretting nicely.

And it's a common suggestion for this kind of problem--quicker than looping
through each cell, too.
 
Top