Date Format Problem in Excel

October 8, 2008 at 10:26:13
Specs: XP, P4/2GB
I have several large trade blotters I exported from our portfolio trading platform. Problem is, with the date, when it exports to excel, it exports the date as a single integer. For example, 05/15/2008 becomes 51508. 11/15/2008 becomes 111508. Pain the butt right?

Is there any possible formula that I can use convert these to regular date format? That way I can use the formula to get the correct date format, then copy special back into the correct column. There are literally thousands of entries so of course I don't want to manually do this.

Thanks in advance!

See More: Date Format Problem in Excel

Report •

October 8, 2008 at 10:58:58
Ok I think I have it almost figured out. I can use a conditional formula to run the date. I found the following formula:


That works for dates that have 6 numbers in it. So then using my super duper excel deduction skills, I made the following formula:


for dates with 5 numbers in it. It works. So now, I need to make the following formula:


Shouldn't that work? Now I have to find the formula that counts how many numbers are in the cell.

EDIT: I tried this formula:

Got it after several edits and tries.

Report •

October 8, 2008 at 12:00:33
I'm curious as to why the import drops the slashes in the first place.

Fix that and you don't need any formulae.

Report •

October 8, 2008 at 13:13:19
It's a dumb program that doesn't import perfectly. That's all I can think of. Our other program does it just fine.

Report •

Related Solutions

Ask Question