Thread: [SOLVED] Problem with years

We have a worksheet with a large number of rows of Purchase Order data. The
5th and 6th characters of order number is year, which I have extracted out
with the Mid function. These years are now in the form of 97 for 1997, 98
for 1998, 00 for 2000, 02 for 2002, etc. The years 1994 through 2005 are
represented.
We need to get the two digit years into four digit, so that, for example, 96
becomes 1996, 01 becomes 2001, etc. I have experimented with some custom
formats for the two digit years, and concatenating within a logic function
but can not get this to work properly.
Any suggestions will be appreciated.
Paul

assuming text, something like this

=IF(LEFT(A3,1)="0","20","19")&A3

or without extracting to a column first
=IF(MID(A5,5,1)="0","20","19")&MID(A5,5,2)

--
Don Guillett
SalesAid Software
donaldb@281.com
"PA" <PA@discussions.microsoft.com> wrote in message
news:9A11A5FE-CEA0-429E-88A2-673AD573AA18@microsoft.com...
> We have a worksheet with a large number of rows of Purchase Order data.
The
> 5th and 6th characters of order number is year, which I have extracted out
> with the Mid function. These years are now in the form of 97 for 1997,
98
> for 1998, 00 for 2000, 02 for 2002, etc. The years 1994 through 2005 are
> represented.
> We need to get the two digit years into four digit, so that, for example,
96
> becomes 1996, 01 becomes 2001, etc. I have experimented with some custom
> formats for the two digit years, and concatenating within a logic function
> but can not get this to work properly.
> Any suggestions will be appreciated.
> Paul

Thanks, works great. Now if I understood what you did.......

"Don Guillett" wrote:

> assuming text, something like this
>
> =IF(LEFT(A3,1)="0","20","19")&A3
>
> or without extracting to a column first
> =IF(MID(A5,5,1)="0","20","19")&MID(A5,5,2)
>
> --
> Don Guillett
> SalesAid Software
> donaldb@281.com
> "PA" <PA@discussions.microsoft.com> wrote in message
> news:9A11A5FE-CEA0-429E-88A2-673AD573AA18@microsoft.com...
> > We have a worksheet with a large number of rows of Purchase Order data.
> The
> > 5th and 6th characters of order number is year, which I have extracted out
> > with the Mid function. These years are now in the form of 97 for 1997,
> 98
> > for 1998, 00 for 2000, 02 for 2002, etc. The years 1994 through 2005 are
> > represented.
> > We need to get the two digit years into four digit, so that, for example,
> 96
> > becomes 1996, 01 becomes 2001, etc. I have experimented with some custom
> > formats for the two digit years, and concatenating within a logic function
> > but can not get this to work properly.
> > Any suggestions will be appreciated.
> > Paul
>
>
>

glad to help. Just look in the HELP index section for left and mid and if.

--
Don Guillett
SalesAid Software
donaldb@281.com
"PA" <PA@discussions.microsoft.com> wrote in message
news:ADC687EB-BECB-48F3-BA46-5E9E7D4D4577@microsoft.com...
> Thanks, works great. Now if I understood what you did.......
>
> "Don Guillett" wrote:
>
> > assuming text, something like this
> >
> > =IF(LEFT(A3,1)="0","20","19")&A3
> >
> > or without extracting to a column first
> > =IF(MID(A5,5,1)="0","20","19")&MID(A5,5,2)
> >
> > --
> > Don Guillett
> > SalesAid Software
> > donaldb@281.com
> > "PA" <PA@discussions.microsoft.com> wrote in message
> > news:9A11A5FE-CEA0-429E-88A2-673AD573AA18@microsoft.com...
> > > We have a worksheet with a large number of rows of Purchase Order
data.
> > The
> > > 5th and 6th characters of order number is year, which I have extracted
out
> > > with the Mid function. These years are now in the form of 97 for
1997,
> > 98
> > > for 1998, 00 for 2000, 02 for 2002, etc. The years 1994 through 2005
are
> > > represented.
> > > We need to get the two digit years into four digit, so that, for
example,
> > 96
> > > becomes 1996, 01 becomes 2001, etc. I have experimented with some
custom
> > > formats for the two digit years, and concatenating within a logic
function
> > > but can not get this to work properly.
> > > Any suggestions will be appreciated.
> > > Paul
> >
> >
> >

Thanks again, staring at it for a few more moments, and the revelation came
to me.


"Don Guillett" wrote:

> glad to help. Just look in the HELP index section for left and mid and if.
>
> --
> Don Guillett
> SalesAid Software
> donaldb@281.com
> "PA" <PA@discussions.microsoft.com> wrote in message
> news:ADC687EB-BECB-48F3-BA46-5E9E7D4D4577@microsoft.com...
> > Thanks, works great. Now if I understood what you did.......
> >
> > "Don Guillett" wrote:
> >
> > > assuming text, something like this
> > >
> > > =IF(LEFT(A3,1)="0","20","19")&A3
> > >
> > > or without extracting to a column first
> > > =IF(MID(A5,5,1)="0","20","19")&MID(A5,5,2)
> > >
> > > --
> > > Don Guillett
> > > SalesAid Software
> > > donaldb@281.com
> > > "PA" <PA@discussions.microsoft.com> wrote in message
> > > news:9A11A5FE-CEA0-429E-88A2-673AD573AA18@microsoft.com...
> > > > We have a worksheet with a large number of rows of Purchase Order
> data.
> > > The
> > > > 5th and 6th characters of order number is year, which I have extracted
> out
> > > > with the Mid function. These years are now in the form of 97 for
> 1997,
> > > 98
> > > > for 1998, 00 for 2000, 02 for 2002, etc. The years 1994 through 2005
> are
> > > > represented.
> > > > We need to get the two digit years into four digit, so that, for
> example,
> > > 96
> > > > becomes 1996, 01 becomes 2001, etc. I have experimented with some
> custom
> > > > formats for the two digit years, and concatenating within a logic
> function
> > > > but can not get this to work properly.
> > > > Any suggestions will be appreciated.
> > > > Paul
> > >
> > >
> > >
>
>
>