Problems with dates

[bloubul]

Hi All

Can any one please help.

I need to format the date in excel from "yy/mm/dd" to "mm/dd/yy"

I have tried "mm/dd/yy;@", in custom format but to no avail, I need to format 7000 rows in column "A1:A7000".

Is there perhaps a macro that I can use, I have search on the Net but can't find any.

I thank you in advance.

BlouBul

 

[AllenB]

I just took a quick look at this and I think this might work
You might have to do this for multiple years but maybe not.
Create a table of all dates for 1 year. In Column B place the date value (These are actually whole numbers expressed as a date format). In columns B,C and D convert the date to the Day, Month, and Year.
In column A enter the formula right(B1,3).
Name the Table something like "Year2016"
Say the date you want to convert is in cell E1.
in F1 Enter the formula Right(E1,3).
In G1 enter the formula =vlookup(F1,Year2016,2) to get the day.
In columns H and I use the same formula with ,3 for month and ,4 for year.

Hope this helps

 

[AllenB]

On second thought all you have to do is break down the date value int Day, Month, Year in adjacent columns.
Cell A1 = Date Value
Cell B1 =day(A1)
Cell C1 =month(A1)
Cell D1 = year(A1)
Cell E1 =B1&C1&D1

 

[AllenB]

This will use 1 column and retain the 0 for days 1-9
Cell A1 = Date Value
Cell B1 = =IF(DAY(A1)<10,0&DAY(A1),DAY(A1))&MONTH(A1)&YEAR(A1)
Sorry for the other Stuff, Still Learning.

 

[bloubul]

Thank you all for the help.

BlouBul