Converting 8 digit numbers into dates

[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....

 

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

 

[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

 

[andy.cotgreave]

Perfect! I've not come across DateSerial before. Very useful!

 

[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

 

[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.

 

[Guest]

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

 

[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.

 

[Lori]

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

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

 

[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

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....

 

[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

 

[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.