change date-format

  • Thread starter Thread starter Yippie
  • Start date Start date
Y

Yippie

In a worksheet I got 3000 cells, all with dates.
The format of these dates looks like mm-dd-yy;
this is an american format; I want to have changed this to dd-mm-yy.
How can I solve this probem?
Y.
 
I often have this problem too and it drives me nuts. I live in England
but often need to download data from US servers. I've set out the way I
get round it below, but I love to know if anyone's got a better way.

1. Suppose cell A2 is mm-dd-yy (Excel date format)
2. Create cell B2 (US Date - Text) as =TEXT($A2,"mm-dd-yy")
3. Create cell C2 (Month) as =LEFT($B2,2)
4. Create cell D2 (Day) as =MID($B2,4,2)
5. Create cell E2 (Year) as =RIGHT($B2,2)
6. Create cell F2 (UK Date) =DATE($E2,$C2,$D2)

Anne's solution doesn't work, I suppose this is because the file has
been imported into a UK computer, Excel thinks that the date is already
in dd-mm-yyyy format.
 
Back
Top