# 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)``

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

year=2019
month=06
day=05

• 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!

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
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!