Converting European date to US dates

[Rosa Campos]

I have European dates: For example: 26.9.81(dd/mm/yy) I
need this converted to MM-dd-yy format.

thanks

rosa

 

[Bernard V Liengme]

Hi Rosa,
Normally all that is needed to change from US <-> Rest of World dates scheme
is to use Format|Cells|Dates, But I suspect the dots in you dates are
causing a problem. You may need to extract the three numeric values and make
a 'date' out of them.

Here is one way with your date in A2 (there could well be better ways -
text functions are not my speciality)
dd(in C2) =MID(A2,1,FIND(".",A2)-1)
mm(in D2)
=MID(A2,FIND(".",A2)+1,FIND(".",A2,FIND(".",A2)+1)-FIND(".",A2)-1)
yy(in E2) =RIGHT(A2,2)
date =DATE(E2,D2,C2)


Having got the correct dates you can use Copy followed by Paste Special as
Values to let you delete all the intermediate stuff.

Bernard

 

[keepitcool]

In addition to Bernard's method of forcing textstrings that look like
dates to date serials:


you may use the =datevalue(a1) function to produce a dateserial.
excel is reasonaly efficient in picking the right format.
however the dot is not often used and most europeans use dash(-)

=DATEVALUE(SUBSTITUTE(C5,".","-")) will efficiently produce a dateserial.



Once they are entred as date serials you wont have any more problems:
if you mean that you got a cell with a date serial that displays in your
cell as dd.mm.yy and (for invoicing or reporting) need to display in US
format.

THEN

note these date "lettercodes" like y or j are LANGUAGE specific when
entered..
but
number formats are translated automatically if a user changes locales,
or if opened by a user in another country.

the textstring in the TEXT formula is a plain string (which you could put
in a named range of in a cell), By coincidence the english TEXT function
is the same in GERMAN excel.

you may use following:
format the cell with a custom format: mm/dd/jjjj (or mm/dd/yyyy depending
on YOUR locale)

<when opened in different locales will auto translate>

If you would have problems with the separators the precede them with a
backslash. Custom format like 0\.0\.0\. or mm\.dd\.yy are used to avoid
the dot being treated as a separator.