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 Fiber has a strong future?

Discuss in The Lounge

Poll History