Date format problem

[Guest]

Hi!;

I have one file export to excel format, all data can export properly in excel format. However I have one problem regarding the date. Due to my country date format is dd/mm/yy and default excel format is mm/dd/yy. So when I used the date to do some date calculation the result becomes #Value!. I try to convert the date format from mm/dd/yy to dd/mm/yy in format cells, however it doesn't success. Anyone has the idea to let me change the date format.

Thanks & Regards

 

[Frank Kabel]

Hi
try the following (as the dates are probably stored as text)
- in an adjacent column (lets say col B) enter the formula
=DATE(--("20" & RIGHT(A1,2)),--MID(A1,4,2),--LEFT(A1,2))
and copy down
- Format this column with your date format
- copy this column and paste it with 'Edit - Paste Special' as 'Values'