How to show excel date custom formate as date

Question

I have in excel cell date in this formate: yyyymmdd (e.g. 20141114)

I tried formating it as but it always shows: #################################################

How to show it as Nov 14, 2014 ?

Answer 1

You can convert the value yyyymmdd to a date mm/dd/yyyy in the cell (assuming) A1 using a formula like this:

=DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))

The string will be now converted to DATE. The desired formatting you can adjust over the context menu "Format cell", e.g. Nov 14, 2014

Answer 2

I tried

=DATEVALUE(TEXT(B1,"0000-00-00"))

It worked OK for me.

Update

=TEXT(DATEVALUE(TEXT(B1,"0000-00-00")),"MMM DD, YYYY")

This is another solution in case we want to directly convet it to text

solution is from: http://www.msofficeforums.com/excel-programming/28491-insert-formula-programatically.html