none
Date Tranformation RRS feed

  • Question

  • Hey guys,

    I have a question about date transfer.

    In an excel spreadsheet, cells(1,1) = 28Feb90. How can I manipulate this data to get cells(1,2) = 199002(meaning Feb, 1990)? It looks like Excel can't read the short 'Feb' as February.

    The data ranges from 31Jan90 t0 40Apr12. So I was wondering if there is a quick way to transfer. Or should I write a user-defined function?

    Thank you and enjoy the rest of the weekend.

    Sean
    Sunday, June 3, 2012 12:58 AM

Answers

  • You can specify a custom format like this:

    • Select the cell(s) that you want to format.
    • Press Ctrl+1 to activate the Format Cells dialog.
    • If necessary, activate the Number tab.
    • Select Custom in the Category list.
    • Enter yyyymm in the Type box.
    • Click OK.

    When you use the TEXT function, the format should be enclosed in quotes:

    =TEXT(A1, "yyyymm")


    Regards, Hans Vogelaar

    • Marked as answer by wenyuanalive Sunday, June 3, 2012 4:21 PM
    Sunday, June 3, 2012 12:57 PM

All replies

  • In B1, enter the formula

    =DATEVALUE(A1)

    and format B1 with the custom number format yyyymm. Fill down as far as needed.

    If you prefer 199002 as a text value in B1, use

    =TEXT(A1,"yyyymm")

    By the way, it will fail for 40Apr12.


    Regards, Hans Vogelaar

    Sunday, June 3, 2012 8:15 AM
  • In B1, enter the formula

    =DATEVALUE(A1)

    and format B1 with the custom number format yyyymm. Fill down as far as needed.

    If you prefer 199002 as a text value in B1, use

    =TEXT(A1,"yyyymm")

    By the way, it will fail for 40Apr12.


    Regards, Hans Vogelaar

    Hey Hans,

    Thanks for your reply!

    However, when I used datevalue(A1), B1 returns a number (I know it's Excel-readable date value) but there is no customer format yyyymm.

    When I used text(A1, yyyymm), it returns #NAME? . 

    I sort of figured it out myself: 

    B1 =IF(MONTH(A1)<10,YEAR(A1)&"0"&MONTH(A1),YEAR(A1)&MONTH(A1))
    Sunday, June 3, 2012 12:39 PM
  • You can specify a custom format like this:

    • Select the cell(s) that you want to format.
    • Press Ctrl+1 to activate the Format Cells dialog.
    • If necessary, activate the Number tab.
    • Select Custom in the Category list.
    • Enter yyyymm in the Type box.
    • Click OK.

    When you use the TEXT function, the format should be enclosed in quotes:

    =TEXT(A1, "yyyymm")


    Regards, Hans Vogelaar

    • Marked as answer by wenyuanalive Sunday, June 3, 2012 4:21 PM
    Sunday, June 3, 2012 12:57 PM
  • You can specify a custom format like this:

    • Select the cell(s) that you want to format.
    • Press Ctrl+1 to activate the Format Cells dialog.
    • If necessary, activate the Number tab.
    • Select Custom in the Category list.
    • Enter yyyymm in the Type box.
    • Click OK.

    When you use the TEXT function, the format should be enclosed in quotes:

    =TEXT(A1, "yyyymm")


    Regards, Hans Vogelaar

    The text method works perfectly well. It will surely fail for 40Apr12 because there is no April 40. However, as for the datevalue method,  I did exactly as you told but it still returns #VALUE!.

    Best, Sean

    Sunday, June 3, 2012 2:20 PM
  • Frankly, I don't understand why the TEXT formula would work if the DATAVALUE formula doesn't - they both rely upon Windows recognizing the value in column A as a valid date.

    Regards, Hans Vogelaar

    Sunday, June 3, 2012 3:09 PM
  • Frankly, I don't understand why the TEXT formula would work if the DATAVALUE formula doesn't - they both rely upon Windows recognizing the value in column A as a valid date.

    Regards, Hans Vogelaar

    I have no clue. It could be something to do with formatting. I used to set my computer into another language and lots of functions didn't work well.

    Again, thanks a lot, you are a real expert !


    Sunday, June 3, 2012 4:21 PM
  • Ah! If Windows is set to another language than English, dates with English month names may not always be recognized.

    Regards, Hans Vogelaar

    Sunday, June 3, 2012 4:53 PM