Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Mar 2004
    Location
    North Carolina, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Extract first 2 numbers from MM/DD/YY data (Excel 2000)

    Hi, I have a column of data in the following format: MM/DD/YY. For example, 10/15/04. In the adjacent column, I'd like to display the 10 only. I have tried = left(x2, 2). Data is in column X... It returns 38275.6137934028... Any ideas on how to extract only a 10 here???

    All help appreciated.

    Thanks.

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

    Re: Extract first 2 numbers from MM/DD/YY data (Excel 2000)

    Use =MONTH(X2)
    Excel stores dates as numbers internally - the number of days since December 31, 1899. So the LEFT function is not useful to extract a portion of a date, but Excel has DAY, MONTH and YEAR functions.

  3. #3
    3 Star Lounger
    Join Date
    Mar 2004
    Location
    North Carolina, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Extract first 2 numbers from MM/DD/YY data (Excel 2000)

    Hans, thanks for your reply... I tried the =month(x2) formula and it returns the following: 1/10/00

    Any other ideas?

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

    Re: Extract first 2 numbers from MM/DD/YY data (Excel 2000)

    Apparently, X2 is now formatted as a date. Select X2, then select Edit | Clear | Formats...

  5. #5
    3 Star Lounger
    Join Date
    Mar 2004
    Location
    North Carolina, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Extract first 2 numbers from MM/DD/YY data (Excel 2000)

    Hans, same result... See attached. By the way, thanks!

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

    Re: Extract first 2 numbers from MM/DD/YY data (Excel 2000)

    Sorry, I should have written: select the cell that contains the formula (instead of X2), and select Edit | Clear | Formats...

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Extract first 2 numbers from MM/DD/YY data (Excel 2000)

    If I select cell D2 in your worksheet, then select Clear from the Edit menu and then select Formats from the fly out menu, the cell displays 10.
    Legare Coleman

  8. #8
    3 Star Lounger
    Join Date
    Mar 2004
    Location
    North Carolina, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Extract first 2 numbers from MM/DD/YY data (Excel 2000)

    Hans, I went into the other cell where I had entered the (cell z2 had the formula) formula = month(x2) and followed your instructions, edit, clear, formats and that is a fix.

    As always, THANKS!

Posting Permissions

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