MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Having trouble converting from english date!


Posted by Rob A on October 05, 2001 7:06 AM

When i try and re-arrange a date from english dd/mm/yy to yy/mm/dd excel won't automatically do this if the day is more than 12. e.g. If i tried to change 10/12/01 it would change it to 2001/12/10 fine, when i try the same with 13/12/01 it won't work. Any advice? I know it can be done manually but i have to change over a thousand dates this way so i'm using a macro.


Posted by Barrie Davidson on October 05, 2001 7:49 AM

Rob, I'm assuming you are converting a text value. If that is the case, you can convert via a formula. Use

=DATE(RIGHT(A1,2)+2000,MID(A1,4,2),LEFT(A1,2))

Copy this down. Then select the entire range containing the formula, copy and paste values.

Hope this helps out.

BarrieBarrie Davidson