Formatting inputted number (190605) to cusstom date format

  • Hi!
    Let me explain:
    in cell B2 i enter 190605 and i want it to show as 19-06-05
    I made it through custom formatting

    Code
    ## - ## - ##


    But excel (office 365) does not recognize it as date because the value of B2 is 190605.
    How can I solve this so I can use B2 for

    Code
    =year (B2)


    Thanks in advance
    Regards
    Peter from Sweden


  • Hi Peter,


    Which part of your value in B2 represents Year, Month and Day?


    Is the year 2019, the month 06 and the day 05; or
    is the year 2005, the month 06 and the day 19


    Regards,


    Gizzmo

  • Try:


    =DATE("20"&LEFT(B2,2),MID(B2,3,2),RIGHT(B2,2))

    Where there is a will there are many ways. Finding one that works for you is the challenge!

  • thanks for y,our reply!
    Your code is of course working, but not really what I'm looking for ....


    If I understood

    Code
    =DATE("20"&LEFT(B2,2),MID(B2,3,2),RIGHT(B2,2))

    correctly, I need a helper column, correct?
    What I am looking for is a formatting of

    Code
    cell B2

    , so I can do this:
    input: 190606
    output: 2019-06-06
    Thanks in advance
    Regards
    Peter

  • Hi Peter,


    Yes helper column/cell is needed with the formula provided by NBVC


    As an Example in cell C2 just put "=YEAR(DATE("20"&LEFT(B2,2),MID(B2,3,2),RIGHT(B2,2)))"


    Change the format to General for cell C2. Result should be "2019". Your original post referring to "=year (B2)" has been interpreted by me as return the year only which is 2019, in a separate cell.


    From your response to NBVC, it appears you are wanting to come up with custom format on cell B2 in the first instance to display "2019-06-05"


    If this is the case then go to Format Cells, Custom and enter 20##-##-##. But please realise that excel will not recognise this as a date!


    Hope this helps.


    Regards,


    Gizzmo

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!