Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    May 2002
    Location
    Smithfield, Free State, South Africa
    Posts
    210
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    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.
    Regards,
    Rudi

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #4
    3 Star Lounger
    Join Date
    May 2002
    Location
    Smithfield, Free State, South Africa
    Posts
    210
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Year = number (Excel 2000/3)

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

  5. #5
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    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
    Cheers,

    Paul Edstein
    [MS MVP - Word]

Posting Permissions

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