| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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? |
|
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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? |
|
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
system wont accept a format
Thank you Sheeloo,
it works great |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Vista oper system will not accept old excell files | Excel Discussion (Misc queries) | |||
| Date format from excel to CVS file wont. Change in CVS wont stay. | Excel Worksheet Functions | |||
| how can I have Excel accept date in format dd/mm/yy ? | Setting up and Configuration of Excel | |||
| adv filter wont accept criteria | Excel Worksheet Functions | |||
| excel 2002 wont accept my code on back of disk | New Users to Excel | |||
Linear Mode
