I have the following requirement. In a cell i have dates that have been
imported
from another system. They are entered as follows,

1206 would be December 2006
605 would be June 2005
0101 would be January 2001
There are no date older than 0101.

I would like to convert the cells or copy to new cells in the following
format 01/12/2006. Because there is no specific day of the month in the
original data i am happy to have 01 as the default.

is this easily achievable?

=DATE(2000+RIGHT(A1,2)*1,LEFT(A1,LEN(A1)-2),1)

--
Gary''s Student
gsnu200710


"Maax" wrote:

> I have the following requirement. In a cell i have dates that have been
> imported
> from another system. They are entered as follows,
>
> 1206 would be December 2006
> 605 would be June 2005
> 0101 would be January 2001
> There are no date older than 0101.
>
> I would like to convert the cells or copy to new cells in the following
> format 01/12/2006. Because there is no specific day of the month in the
> original data i am happy to have 01 as the default.
>
> is this easily achievable?
>

try this. Notice the ONE dot before the items in the with statement.
Sub dodatefromtext()
For Each c In Selection
With c
.Value = DateSerial(Right(c, 2), Left(c, Len(c) - 2), 1)
.NumberFormat = "dd/mm/yyyy"
End With
Next c
End Sub


--
Don Guillett
SalesAid Software
(E-Mail Removed)
"Maax" <(E-Mail Removed)> wrote in message
news:2B9A7D74-D265-4D59-B4FA-(E-Mail Removed)...
>I have the following requirement. In a cell i have dates that have been
> imported
> from another system. They are entered as follows,
>
> 1206 would be December 2006
> 605 would be June 2005
> 0101 would be January 2001
> There are no date older than 0101.
>
> I would like to convert the cells or copy to new cells in the following
> format 01/12/2006. Because there is no specific day of the month in the
> original data i am happy to have 01 as the default.
>
> is this easily achievable?
>

Orginal posting of the question answered.

--
Regards,
Tom Ogilvy


"Maax" wrote:

> I have the following requirement. In a cell i have dates that have been
> imported
> from another system. They are entered as follows,
>
> 1206 would be December 2006
> 605 would be June 2005
> 0101 would be January 2001
> There are no date older than 0101.
>
> I would like to convert the cells or copy to new cells in the following
> format 01/12/2006. Because there is no specific day of the month in the
> original data i am happy to have 01 as the default.
>
> is this easily achievable?
>

Just some added information:
Gary's response provides an actual date serial number which can be used in
further calculations. To have it appear as you specify, you would need to
format the cell with the formula with the appropriate date format you want to
see.

--
Regards,
Tom Ogilvy

"Gary''s Student" wrote:

> =DATE(2000+RIGHT(A1,2)*1,LEFT(A1,LEN(A1)-2),1)
>
> --
> Gary''s Student
> gsnu200710
>
>
> "Maax" wrote:
>
> > I have the following requirement. In a cell i have dates that have been
> > imported
> > from another system. They are entered as follows,
> >
> > 1206 would be December 2006
> > 605 would be June 2005
> > 0101 would be January 2001
> > There are no date older than 0101.
> >
> > I would like to convert the cells or copy to new cells in the following
> > format 01/12/2006. Because there is no specific day of the month in the
> > original data i am happy to have 01 as the default.
> >
> > is this easily achievable?
> >

Hi Gary, thanks very much, exactly what i needed.

"Gary''s Student" wrote:

> =DATE(2000+RIGHT(A1,2)*1,LEFT(A1,LEN(A1)-2),1)
>
> --
> Gary''s Student
> gsnu200710
>
>
> "Maax" wrote:
>
> > I have the following requirement. In a cell i have dates that have been
> > imported
> > from another system. They are entered as follows,
> >
> > 1206 would be December 2006
> > 605 would be June 2005
> > 0101 would be January 2001
> > There are no date older than 0101.
> >
> > I would like to convert the cells or copy to new cells in the following
> > format 01/12/2006. Because there is no specific day of the month in the
> > original data i am happy to have 01 as the default.
> >
> > is this easily achievable?
> >