Custom date format without spaces or punctuation

**doowle** · 07-10-2012, 11:08 PM

Is there a way to put a custom format for dates that would result in yyyymmdd? ie, Feb 1 2012 would read 20120201? Nothing I try seems to work.

**Paul** · 07-10-2012, 11:15 PM

Hi Doowle, welcome to the forum.

If your cell contains a true date value, just right-click on it and choose Format Cells. Select Custom, and then beneath "Type:", enter the format you want, e.g. yyyymmdd

Click OK and you're done.

**doowle** · 07-11-2012, 12:19 AM

Thanks. I'm sorry, I phrased that really poorly. I am pasting in text values, but I want them to convert to actual dates while still displaying as yyyymmdd. So I need a way to have it both convert the value I paste into date format, but apply the custom format so it continues to look the same. I did apply the custom formatting, but it reads what I paste in as a text value.
ie I am copying 20120201 from a different program and pasting it into a cell which has the yyyymmdd custom format applied to it. But because it reads what I paste in as text, it doesn't display it. It just gives me #### across the cell.

**cronos** · 07-11-2012, 02:58 AM

paste your date text in cell A1, then add the following formula in cell B1 (formatted as yyyymmdd)

=DATEVALUE(MID(A1,1,4)&"/"&MID(A1,5,2)&"/"&MID(A1,7,2))