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

How concerned are you about the OpenSSL "Heartbleed" bug?

Discuss in The Lounge

Poll History