# 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

#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