Converting text to yy/mm
 

I have a regular data that I import. Data that is input to column F comes in
as formatted as text with quotes around it ( i.e. "0601"). (If it helps, the
column to the right of this data is blank.) I have no control over the
imported formatting so it needs to be converted. This data should be
formatted as a date with yy/dd format. The resulting date from the initial
data would be 06/01 I need all the help I can get developing a macro that
will accomplish this.

Converting text to yy/mm
 

by yy/dd format, do you mean 06/34 would be Feb 3, 2006?

Please elaborate.

--
Regards,
Tom Ogilvy

"Billy B" wrote in message
...
I have a regular data that I import. Data that is input to column F comes
in
as formatted as text with quotes around it ( i.e. "0601"). (If it helps,
the
column to the right of this data is blank.) I have no control over the
imported formatting so it needs to be converted. This data should be
formatted as a date with yy/dd format. The resulting date from the initial
data would be 06/01 I need all the help I can get developing a macro that
will accomplish this.

Converting text to yy/mm
 

Tom,
Sorry, I screwed the date format up in my post. It should format yy/mm.

"Tom Ogilvy" wrote:

by yy/dd format, do you mean 06/34 would be Feb 3, 2006?

Please elaborate.

--
Regards,
Tom Ogilvy

"Billy B" wrote in message
...
I have a regular data that I import. Data that is input to column F comes
in
as formatted as text with quotes around it ( i.e. "0601"). (If it helps,
the
column to the right of this data is blank.) I have no control over the
imported formatting so it needs to be converted. This data should be
formatted as a date with yy/dd format. The resulting date from the initial
data would be 06/01 I need all the help I can get developing a macro that
will accomplish this.

Converting text to yy/mm
 

Sub FixData()
Dim s as String, s1 as String
for each cell in selection
s = replace(cell.value,chr(34),"")
s1 = right(s,2) & "/01/20" & lef(s,2)
cell.Value = dateValue(s1)
cell.Numberformat = "yy/mm"
Next
End Sub

Select the data and run the macro.

Test it on a copy of your data.


--
Regards,
Tom Ogilvy


"Billy B" wrote in message
...
Tom,
Sorry, I screwed the date format up in my post. It should format yy/mm.

"Tom Ogilvy" wrote:

by yy/dd format, do you mean 06/34 would be Feb 3, 2006?

Please elaborate.

--
Regards,
Tom Ogilvy

"Billy B" wrote in message
...
I have a regular data that I import. Data that is input to column F
comes
in
as formatted as text with quotes around it ( i.e. "0601"). (If it
helps,
the
column to the right of this data is blank.) I have no control over the
imported formatting so it needs to be converted. This data should be
formatted as a date with yy/dd format. The resulting date from the
initial
data would be 06/01 I need all the help I can get developing a macro
that
will accomplish this.