Converting European date to US dates

  • Thread starter Thread starter Rosa Campos
  • Start date Start date
R

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
 
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
 
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.
 
Back
Top