how can i change european date format to american

P

pclifford99

I've been trying to convert my data format from day/month/year to
month/day/year. I have played with the custom feature, the date function,
etc...... i can't get excel to make the proper conversion and could use some
input.
 
P

pclifford99

for whatever reason, that does not work. i've tried it about a dozen times.
i've cut and pasted "values only" to other parts of my spreadsheet thinking
maybe the format was corrupted, but no go. any furthur ideas ?

thanks
 
G

Gord Dibben

Perhaps your dates are text and not dates.

Try Data>Text to Columns>Next>Next>Column Data Format>Date>MDY and Finish.


Gord Dibben MS Excel MVP
 
P

pclifford99

That did not work either. let me expalin in greater detail. i pulled some
data off a european website. i copy/pasted the data into my excel file. I
suspect when i pasted it i was taking the european date format and pasting as
the default u.s. format. could this be the issue ?

i am about to start pulling my hair out !

thanks
 
F

Fred Smith

Pasting will not change your default format. Also, pasting only stores a value,
it does not apply a format.

Try typing in a date, and then applying the same format to it. If this works, it
means the format you are using is correct, and therefore the problem is your
dates are text, not dates.

Text to columns will solve your problem, if used properly, but if you don't like
that, try creating a helper column with the formula:

=a1+0

That will force excel to convert the date to a serial number, and your format
should work.
 
P

pclifford99

there goes one clump is hair. i am confused by your solution so don't even
know where/how to start.
 
G

Gord Dibben

If your dates are real, which I suspect is not the case, =ISNUMBER(cellref) wil
return TRUE.

If not real....FALSE


Gord

there goes one clump is hair. i am confused by your solution so don't even
know where/how to start.
Click to expand...

Gord Dibben MS Excel MVP
 
P

pclifford99

Got it, thanks. I went back and tried DMY. that convererted it to the format
i needed. I started with data listed as day/month/year so i am not sure why
entering DMY would convert to (month/day/year). I'd be curious just to know
and learn something. any idea ?

thanks
phil
 
G

Gord Dibben

I never know which to try first so I try DMY and see what happens.

Then MDY or YMD or whatever.

I think there is a system but I haven't found it yet.


Gord
 
Top