Problem with years

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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)
 
glad to help. Just look in the HELP index section for left and mid and if.
 
Thanks again, staring at it for a few more moments, and the revelation came
to me.
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Complete Tax Years 1
Date Difference in Complete Tax Years 1
converting text into years 4
Number of days falling in various years 4
Access 2 Digit year in Access Text Box 3
Excel date intervals look up 1
League age chart 4
max values in two ranges 4

Back
Top