Formating dates in excel

May 13, 2009 at 06:24:35
Specs: Windows XP
I have a quick question for an excel expert.

I have date as 20040314

How do I format it to be 03/14/2004?

Please help.

I saw this previous post that gave the answer to use this formula =LEFT(A1,4)&"/"&(MID(A1,5,2)&"/"&RIGHT(A1,2)) and they said it worked but where do I put the formula. If I put in the formula bar it doesn't work

See More: Formating dates in excel

Report •

May 13, 2009 at 06:46:07
Actually, for your string you would need to use:


With your string in A1, click on the cell where you want to see 03/14/2004 and paste in the formula. It can not be the same cell where you have 20040314, as explained below.

This is not really "formatting" a date, as much as it is actually parsing a string of characters and making look like a date. Excel will not recognize 20040314 as a date, so it can't be formatted as such.

In fact, the resulting string of 03/14/2004 is not a date either - it's a text string. However, depending on what you want to do with it, Excel might treat it as a date.

For example, you can't use a standard date format on it to produce something like 14-Mar-2004, but you could add 1 to it to produce 15-Mar-2004 with the proper formatting. You could even add 1 and subtract 1 to get 14-Mar-2004.

Both of these will "force" excel to treat the resulting text string as a date:



Working with dates in Excel can be pretty complicated.

Report •
Related Solutions

Ask Question