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.

✔ Best Answer

Try this formula: =LEFT(A1,4)&"/"&(MID(A1,5,2)&"/"&RIGHT(A1,2))

What format is the cell in now?

Thanks for your quick reply. There is no format. Just general number. The cell is now showing as "20040314". I tried to go to command cell format and date. But it's not giving me anything but this - ######. Any tips ?

Click format, cells, number tab, click date and select the type you want.

That's what I did. I selected "Date" type. However, I am not getting any number. All I get is this sign ########### ! If you type

20040314 in any cell, then do the steps you said to get 03/14/2004, you will not go anywhere.

Enter the date as X/XX/XX or you can use dashes, then hit enter it will appear as you selected in the type of date in format.

Thats a good idea If you input the data by yourself. But I have a file that has over 10,000 date in the format I mentioned, which is 20040314 without any dash or anything. I do not want to input 10,000 date again with dashes. Do you know any other way to format simple number that already there into a date format?

Okay. Now I understand what you are trying to do.

The only way I know of is to use VBA code. You would have to convert the raw number into a format that Excel would recognize as a date 20051001 into 01/10/2005 for instance. Otherwise Excel is just going to assume it is the number 20051001 and since the cell is looking for a date for a date it will just show #########.Maybe someone alse knows a way around this, but I can't think of anything else that would work.

Thanks ! Could you let me know how to do that in VBA. I never had to do it. Is VBA part of Excel ?

Try this formula: =LEFT(A1,4)&"/"&(MID(A1,5,2)&"/"&RIGHT(A1,2))

It's working. Thanks a lot. I love this forum. I guess I get to keep my job then:-))

Use the following and y'll get the answer in the dateformat you put into your computer

(; could be , at your end)

=DATE(LEFT(A1;4);MID(A1;5;2);RIGHT(A1;2))It's more or less the same as rhawk's

OK we have the same problem. I need a little step by step

explanation about where to put the formula. We have a

column of dates, how do we set up the spreadsheet to

compute them all?

Ask Your Question

Weekly Poll