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

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)

wrote in message
ups.com...
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....

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


Bob Phillips wrote:

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)

wrote in message
ups.com...
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....

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

wrote:

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

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

Pete_UK wrote:

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

wrote:

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

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.

wrote:

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

--

Dave Peterson

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 wrote:

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.

wrote:

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

--

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.

wrote:

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 wrote:

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.

wrote:

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

--

Dave Peterson

--

Dave Peterson

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

--
Gary's Student


" wrote:

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

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


wrote in message
ups.com...
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....

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" wrote in message
...
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


wrote in message
ups.com...
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....

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

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


wrote:

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