system wont accept a format
 

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

system wont accept a format
 

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" wrote:

Hi everyone,
There is something in my system other than regional settings that
doesnt 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

system wont accept a format
 

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?

system wont accept a format
 

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" wrote:

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?

system wont accept a format
 

Thank you Sheeloo,
it works great