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 •


#1
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:

=VALUE(LEFT(C1,2)&"/"&MID(C1,3,2)&"/"&RIGHT(C1,2))

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

=VALUE(LEFT(C1,1)&"/"&MID(C1,3,2)&"/"&RIGHT(C1,2))

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

=IF(XXX,VALUE(LEFT(C1,1)&"/"&MID(C1,3,2)&"/"&RIGHT(C1,2)),VALUE(LEFT(C1,2)&"/"&MID(C1,3,2)&"/"&RIGHT(C1,2)))

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:
=IF(LEN(D4)=5,VALUE(LEFT(D4,1)&"/"&MID(D4,2,2)&"/"&RIGHT(D4,2)),VALUE(LEFT(D4,2)&"/"&MID(D4,3,2)&"/"&RIGHT(D4,2)))

Got it after several edits and tries.


Report •

#2
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 •

#3
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