# 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 20040314How 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

#1
May 13, 2009 at 06:46:07
 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)))*1Working with dates in Excel can be pretty complicated.

Report •
Related Solutions