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!
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.
I'm curious as to why the import drops the slashes in the first place. Fix that and you don't need any formulae.
It's a dumb program that doesn't import perfectly. That's all I can think of. Our other program does it just fine.
