system wont accept a format

[Totti]

Hi everyone,
There is something in my system other than regional settings that
doesn’t allow me to read dates in the "mm.dd.yyyy" format, like even
if I try to enter such a format in the custom menu excel automatically
switches it back to "dd.mm.yyyy"
my regional settings are set to USA, even though i Live in Greece.
Any hints please?
Thanks in advance

 

[Sheeloo]

Enter the number 39768 in a few cells
Format each cell in a different date format and let us know if you still get
the same date format in all of them...
Also let us know what is the date you see.

 

[Totti]

actually i have a table with intervals of dates, like this:
01.07.2006 - 03.19.2006
03.06.2006 - 03.18.2006
03.14.2006 - 06.30.2006
05.24.2006 - 09.12.2006
I go to split it in order to have a numerical range so i can tell if a
date falls in this interval and i do the following

=LEFT(A2,10) and try to get their numerical value so i get
01.07.2006 #VALUE!
03.06.2006 #VALUE!
03.14.2006 #VALUE!
05.24.2006 #VALUE!
09.13.2006 #VALUE!

i try at least to substitute the "." with"-" and then i get such a
result:

01-07-2006 38899
03-06-2006 38871
03-14-2006 #VALUE!
05-24-2006 #VALUE!
09-13-2006 #VALUE!

so it is confused between "dd-mm-yy" and "mm-dd-yy" treating both the
same and getting totally error values and in the best case a numerical
value which is incorrect. what should i do?

 

[Sheeloo]

Your computer is set to dd/mm/yyyy format that is why 01.07.2006 is
interpreted as 1-Jul-2006 (38899)

Use this formula to convert to date in A2 (as it is mm-dd-yyyy format)
=DATE(MID(A2,7,4),MID(A2,1,2),MID(A2,4,2))
and copy down

If the above is in C2 you can format Col C in any date format you want...

 

[Totti]

Thank you Sheeloo,
it works great