# Thread: Year = number (Excel 2000/3)

1. ## Year = number (Excel 2000/3)

Hi
I have a list of months and year in the format yyyy mmmm eg January 2004 etc.
In the next column I want to have a number that starts with the year number 401 will be for January 412 will be December 2004, 501 will be January 2005 etc.
What formula can I use to extract the 4 of 2004 and then add in the 01 behind it? This I will copy down the column so that it will change to 501 when it finds January 2005, otherwise it will take the number above and add 1 i.e if B2 contains 401 for January 2004 in A2, then B3 will read =B2+1 giving 402.
Regards

2. ## Re: Year = number (Excel 2000/3)

If you have the dates in the A column, type =A1 in cell B1 and format the cell with a custom number format. Select cell B1 and go to Format Cells. In the Numbers Tab of the Forat Cells Dialog, choose Custom in the Categories List. Type ymm in the Text Box and choose OK. This formats the date to ymm ie 0101, or 0412 etc.
If you are working with dates (esp months, you must type 30 or 31 to change the month in the new format. You won't be able to type just 1 to change a month value. Dates are stored as values theselves, and months are represented by the value 30 or 31.

3. ## Re: Year = number (Excel 2000/3)

Assuming that your list contains actual dates and starts in A2:

A very simple possibility that does not quite conform to your request is to enter =A2 in cell B2, and format B2 with the custom format yymm. This will display as 0401. Fill down as far as needed.

Another possibility is to enter =MOD(YEAR(A2),100)*100+MONTH(A2) in B2, clear the formatting (Excel will probably try to format B2 as a date), and fill down as far as needed.

Post back if this does not work for you.

4. ## Re: Year = number (Excel 2000/3)

Thanks for all the input. Got the answer I need.
Regars

5. ## Re: Year = number (Excel 2000/3)

Hi Lynden,

If A1 contains 'January 2004' etc as text strings, the formula:
=TEXT(DATEVALUE(1&"/"&(LEFT(A1,3)&"/"&RIGHT(A1,4))),"ymm")
will return what you're after for A1. For A2 and down, you need something like:
=IF(A2="",TEXT(DATEVALUE(1&"/"&MOD(RIGHT(B1,2),12)+1&"/"&LEFT(B1,2)+(RIGHT(B1,2)="12")),"ymm"),TEXT(DATEV ALUE(1&"/"&(LEFT(A2,3)&"/"&RIGHT(A2,4))),"ymm"))

Cheers

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•