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

Actually, for your string you would need to use: =MID(A1,5,2)&"/"&RIGHT(A1,2)&"/"&(LEFT(A1,4))

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:

=(MID(A1,5,2)&"/"&RIGHT(A1,2)&"/"&(LEFT(A1,4)))+1-1

=(MID(A1,5,2)&"/"&RIGHT(A1,2)&"/"&(LEFT(A1,4)))*1

Working with dates in Excel can be pretty complicated.

Ask Your Question

Weekly Poll

Do you think Google should sell budget phones in the US?

Discuss in The Lounge

Poll History