Solved Date format in Excel

June 1, 2005 at 16:13:31
Specs: Windows 2000, 512

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.


See More: Date format in Excel

Report •


✔ Best Answer
June 2, 2005 at 11:02:18

Try this formula:

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



#1
June 1, 2005 at 16:55:02

What format is the cell in now?

Report •

#2
June 1, 2005 at 17:05:24

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 ?


Report •

#3
June 1, 2005 at 17:43:00

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

Report •

Related Solutions

#4
June 1, 2005 at 17:48:14

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.

Report •

#5
June 1, 2005 at 22:35:23

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.

Report •

#6
June 2, 2005 at 08:10:34

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?

Report •

#7
June 2, 2005 at 10:08:49

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.


Report •

#8
June 2, 2005 at 10:20:29

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



Report •

#9
June 2, 2005 at 11:02:18
✔ Best Answer

Try this formula:

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


Report •

#10
June 2, 2005 at 11:11:32

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


Report •

#11
June 2, 2005 at 12:35:49

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


Report •

#12
July 28, 2005 at 11:10:09

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?

Report •


Ask Question