Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date Time formatting problem
Hi all,
I am using Excel 2003 with Vista Business. International settings and date/time format set to United States. I am setting a "custom format" in a cell as dd mmm yy hh:mm. When the cell is empty the Type Function evaluates to 1 (numeric). I enter a date and time say 1/1/08 23:45 and this appears in the cell in exactly the same manner. The Type Function changes automatically and now evaluates to 2 as Text. If I enter a date without a time say 1/1/08 this appears as 1 Jan 08 00:00:00 (using same format as before dd mmm yy hh:mm). In the formula bar it appears as 01 01 2008 12:00:00 AM. I have workbooks developed in Excel 2000 with Windows XP which were working fine. Now with the same WkBks, but using Excel 2003, all of a sudden nothing with date/time in the same cell works. All formats have converted to Text. calculations cannot be performed. Am I missing something here which has changed between systems?? Thanks in advance/sgl |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date Time formatting problem
Did you save the file as CSV? CSF doesn't support formating or formulas. A
CSV file is really a text file that you can view with a text ditor like notepad. "sgl" wrote: Hi all, I am using Excel 2003 with Vista Business. International settings and date/time format set to United States. I am setting a "custom format" in a cell as dd mmm yy hh:mm. When the cell is empty the Type Function evaluates to 1 (numeric). I enter a date and time say 1/1/08 23:45 and this appears in the cell in exactly the same manner. The Type Function changes automatically and now evaluates to 2 as Text. If I enter a date without a time say 1/1/08 this appears as 1 Jan 08 00:00:00 (using same format as before dd mmm yy hh:mm). In the formula bar it appears as 01 01 2008 12:00:00 AM. I have workbooks developed in Excel 2000 with Windows XP which were working fine. Now with the same WkBks, but using Excel 2003, all of a sudden nothing with date/time in the same cell works. All formats have converted to Text. calculations cannot be performed. Am I missing something here which has changed between systems?? Thanks in advance/sgl |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date Time formatting problem
No this is a straight .xls file. I have opened an old xls workbook where a
template had been developed in Excel 2000 and tried to enter data in the date/time formatted cells and this problem came to light. Everything was converting to text either when I enetered data in blank new cells or altering already completed cells. I then tested this in a brand new workbook formatting new cells as I have explained and the same thing happened. Totally confused I am!!!! "joel" wrote: Did you save the file as CSV? CSF doesn't support formating or formulas. A CSV file is really a text file that you can view with a text ditor like notepad. "sgl" wrote: Hi all, I am using Excel 2003 with Vista Business. International settings and date/time format set to United States. I am setting a "custom format" in a cell as dd mmm yy hh:mm. When the cell is empty the Type Function evaluates to 1 (numeric). I enter a date and time say 1/1/08 23:45 and this appears in the cell in exactly the same manner. The Type Function changes automatically and now evaluates to 2 as Text. If I enter a date without a time say 1/1/08 this appears as 1 Jan 08 00:00:00 (using same format as before dd mmm yy hh:mm). In the formula bar it appears as 01 01 2008 12:00:00 AM. I have workbooks developed in Excel 2000 with Windows XP which were working fine. Now with the same WkBks, but using Excel 2003, all of a sudden nothing with date/time in the same cell works. All formats have converted to Text. calculations cannot be performed. Am I missing something here which has changed between systems?? Thanks in advance/sgl |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date Time formatting problem
I need more info.
1) When did the problem start occuring. After Vista was installed. 2) Does the problem happen with more than one PC? 3) Verify you are still running with Excel 2003 using menu Help Info 4) Try opening Excel from Start Menu. does same thing happen? 5) check where the excel short cut is getting the executable. right click shortcut and check properties 6) Do a seach on your C: drive for exce.exe and see if you have more then one verion of the exe. Try opening each version ans see if the same thing happens 7) Make sure tthe extension of the file is XLS. 8) Make sure there arre no macros in your XLSTART directory that is causing the problem. these are some quick items to check to help locate the problem. I checked the Tools - Options for some setting that may explain your problems but didn't find any. "sgl" wrote: No this is a straight .xls file. I have opened an old xls workbook where a template had been developed in Excel 2000 and tried to enter data in the date/time formatted cells and this problem came to light. Everything was converting to text either when I enetered data in blank new cells or altering already completed cells. I then tested this in a brand new workbook formatting new cells as I have explained and the same thing happened. Totally confused I am!!!! "joel" wrote: Did you save the file as CSV? CSF doesn't support formating or formulas. A CSV file is really a text file that you can view with a text ditor like notepad. "sgl" wrote: Hi all, I am using Excel 2003 with Vista Business. International settings and date/time format set to United States. I am setting a "custom format" in a cell as dd mmm yy hh:mm. When the cell is empty the Type Function evaluates to 1 (numeric). I enter a date and time say 1/1/08 23:45 and this appears in the cell in exactly the same manner. The Type Function changes automatically and now evaluates to 2 as Text. If I enter a date without a time say 1/1/08 this appears as 1 Jan 08 00:00:00 (using same format as before dd mmm yy hh:mm). In the formula bar it appears as 01 01 2008 12:00:00 AM. I have workbooks developed in Excel 2000 with Windows XP which were working fine. Now with the same WkBks, but using Excel 2003, all of a sudden nothing with date/time in the same cell works. All formats have converted to Text. calculations cannot be performed. Am I missing something here which has changed between systems?? Thanks in advance/sgl |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date Time formatting problem
Here goes,
1 - This is a new machine Lenovo T500 which came with Vista Business preloaded. Also Office 2007 was preloaded. I uninstalled this as I was developing in Excel 2003 and there were conflicts between the two versions especially with Outlook. Whether all files have been deleted from system cannot check. I get a lot of crashes with Excel 2003/Vista which I do not get using Windows XP Pro. 2 - Need to check this as but do not have another PC available 3 - Checked this is certainly an Excel 2003 version 4 - Same problem as before 5 - Excel shortcut leads to MS Office Excel 2003 6 - Only one version there 7 - Extension is .xls 8 - Only files in this folder are Personal.xls - checked that and nothing there thank you/sgl "joel" wrote: I need more info. 1) When did the problem start occuring. After Vista was installed. 2) Does the problem happen with more than one PC? 3) Verify you are still running with Excel 2003 using menu Help Info 4) Try opening Excel from Start Menu. does same thing happen? 5) check where the excel short cut is getting the executable. right click shortcut and check properties 6) Do a seach on your C: drive for exce.exe and see if you have more then one verion of the exe. Try opening each version ans see if the same thing happens 7) Make sure tthe extension of the file is XLS. 8) Make sure there arre no macros in your XLSTART directory that is causing the problem. these are some quick items to check to help locate the problem. I checked the Tools - Options for some setting that may explain your problems but didn't find any. "sgl" wrote: No this is a straight .xls file. I have opened an old xls workbook where a template had been developed in Excel 2000 and tried to enter data in the date/time formatted cells and this problem came to light. Everything was converting to text either when I enetered data in blank new cells or altering already completed cells. I then tested this in a brand new workbook formatting new cells as I have explained and the same thing happened. Totally confused I am!!!! "joel" wrote: Did you save the file as CSV? CSF doesn't support formating or formulas. A CSV file is really a text file that you can view with a text ditor like notepad. "sgl" wrote: Hi all, I am using Excel 2003 with Vista Business. International settings and date/time format set to United States. I am setting a "custom format" in a cell as dd mmm yy hh:mm. When the cell is empty the Type Function evaluates to 1 (numeric). I enter a date and time say 1/1/08 23:45 and this appears in the cell in exactly the same manner. The Type Function changes automatically and now evaluates to 2 as Text. If I enter a date without a time say 1/1/08 this appears as 1 Jan 08 00:00:00 (using same format as before dd mmm yy hh:mm). In the formula bar it appears as 01 01 2008 12:00:00 AM. I have workbooks developed in Excel 2000 with Windows XP which were working fine. Now with the same WkBks, but using Excel 2003, all of a sudden nothing with date/time in the same cell works. All formats have converted to Text. calculations cannot be performed. Am I missing something here which has changed between systems?? Thanks in advance/sgl |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date Time formatting problem
Just checked on a Windows XP Pro with Excel 2003 and it works fine. So seems
that the common link is Vista. The question is how do I solve this??? "sgl" wrote: Here goes, 1 - This is a new machine Lenovo T500 which came with Vista Business preloaded. Also Office 2007 was preloaded. I uninstalled this as I was developing in Excel 2003 and there were conflicts between the two versions especially with Outlook. Whether all files have been deleted from system cannot check. I get a lot of crashes with Excel 2003/Vista which I do not get using Windows XP Pro. 2 - Need to check this as but do not have another PC available 3 - Checked this is certainly an Excel 2003 version 4 - Same problem as before 5 - Excel shortcut leads to MS Office Excel 2003 6 - Only one version there 7 - Extension is .xls 8 - Only files in this folder are Personal.xls - checked that and nothing there thank you/sgl "joel" wrote: I need more info. 1) When did the problem start occuring. After Vista was installed. 2) Does the problem happen with more than one PC? 3) Verify you are still running with Excel 2003 using menu Help Info 4) Try opening Excel from Start Menu. does same thing happen? 5) check where the excel short cut is getting the executable. right click shortcut and check properties 6) Do a seach on your C: drive for exce.exe and see if you have more then one verion of the exe. Try opening each version ans see if the same thing happens 7) Make sure tthe extension of the file is XLS. 8) Make sure there arre no macros in your XLSTART directory that is causing the problem. these are some quick items to check to help locate the problem. I checked the Tools - Options for some setting that may explain your problems but didn't find any. "sgl" wrote: No this is a straight .xls file. I have opened an old xls workbook where a template had been developed in Excel 2000 and tried to enter data in the date/time formatted cells and this problem came to light. Everything was converting to text either when I enetered data in blank new cells or altering already completed cells. I then tested this in a brand new workbook formatting new cells as I have explained and the same thing happened. Totally confused I am!!!! "joel" wrote: Did you save the file as CSV? CSF doesn't support formating or formulas. A CSV file is really a text file that you can view with a text ditor like notepad. "sgl" wrote: Hi all, I am using Excel 2003 with Vista Business. International settings and date/time format set to United States. I am setting a "custom format" in a cell as dd mmm yy hh:mm. When the cell is empty the Type Function evaluates to 1 (numeric). I enter a date and time say 1/1/08 23:45 and this appears in the cell in exactly the same manner. The Type Function changes automatically and now evaluates to 2 as Text. If I enter a date without a time say 1/1/08 this appears as 1 Jan 08 00:00:00 (using same format as before dd mmm yy hh:mm). In the formula bar it appears as 01 01 2008 12:00:00 AM. I have workbooks developed in Excel 2000 with Windows XP which were working fine. Now with the same WkBks, but using Excel 2003, all of a sudden nothing with date/time in the same cell works. All formats have converted to Text. calculations cannot be performed. Am I missing something here which has changed between systems?? Thanks in advance/sgl |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date Time formatting problem
Adjust your date format in Windows (Control Panel). Excel recognizes dates
that Windows recognizes. -- Kind regards, Niek Otten Microsoft MVP - Excel "sgl" wrote in message ... Just checked on a Windows XP Pro with Excel 2003 and it works fine. So seems that the common link is Vista. The question is how do I solve this??? "sgl" wrote: Here goes, 1 - This is a new machine Lenovo T500 which came with Vista Business preloaded. Also Office 2007 was preloaded. I uninstalled this as I was developing in Excel 2003 and there were conflicts between the two versions especially with Outlook. Whether all files have been deleted from system cannot check. I get a lot of crashes with Excel 2003/Vista which I do not get using Windows XP Pro. 2 - Need to check this as but do not have another PC available 3 - Checked this is certainly an Excel 2003 version 4 - Same problem as before 5 - Excel shortcut leads to MS Office Excel 2003 6 - Only one version there 7 - Extension is .xls 8 - Only files in this folder are Personal.xls - checked that and nothing there thank you/sgl "joel" wrote: I need more info. 1) When did the problem start occuring. After Vista was installed. 2) Does the problem happen with more than one PC? 3) Verify you are still running with Excel 2003 using menu Help Info 4) Try opening Excel from Start Menu. does same thing happen? 5) check where the excel short cut is getting the executable. right click shortcut and check properties 6) Do a seach on your C: drive for exce.exe and see if you have more then one verion of the exe. Try opening each version ans see if the same thing happens 7) Make sure tthe extension of the file is XLS. 8) Make sure there arre no macros in your XLSTART directory that is causing the problem. these are some quick items to check to help locate the problem. I checked the Tools - Options for some setting that may explain your problems but didn't find any. "sgl" wrote: No this is a straight .xls file. I have opened an old xls workbook where a template had been developed in Excel 2000 and tried to enter data in the date/time formatted cells and this problem came to light. Everything was converting to text either when I enetered data in blank new cells or altering already completed cells. I then tested this in a brand new workbook formatting new cells as I have explained and the same thing happened. Totally confused I am!!!! "joel" wrote: Did you save the file as CSV? CSF doesn't support formating or formulas. A CSV file is really a text file that you can view with a text ditor like notepad. "sgl" wrote: Hi all, I am using Excel 2003 with Vista Business. International settings and date/time format set to United States. I am setting a "custom format" in a cell as dd mmm yy hh:mm. When the cell is empty the Type Function evaluates to 1 (numeric). I enter a date and time say 1/1/08 23:45 and this appears in the cell in exactly the same manner. The Type Function changes automatically and now evaluates to 2 as Text. If I enter a date without a time say 1/1/08 this appears as 1 Jan 08 00:00:00 (using same format as before dd mmm yy hh:mm). In the formula bar it appears as 01 01 2008 12:00:00 AM. I have workbooks developed in Excel 2000 with Windows XP which were working fine. Now with the same WkBks, but using Excel 2003, all of a sudden nothing with date/time in the same cell works. All formats have converted to Text. calculations cannot be performed. Am I missing something here which has changed between systems?? Thanks in advance/sgl |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date Time formatting problem
Nick I have already tried that. My Regional Settings are as follows:
Formats English (United States) Short date dd mmm yy Long date dd mmmm yy Time HH:mm:ss (24 hours) I have the same settings on a computer with Office 2003 and Windows XP Pro where calculations perform correctly. With Vista Business and Excel 2003 all entries made in a Custom setting of dd mm yy HH:mm convert to Text once you enter time, say 1 Jan 08 23:45, which makes calculations impossible! sgl "Niek Otten" wrote: Adjust your date format in Windows (Control Panel). Excel recognizes dates that Windows recognizes. -- Kind regards, Niek Otten Microsoft MVP - Excel "sgl" wrote in message ... Just checked on a Windows XP Pro with Excel 2003 and it works fine. So seems that the common link is Vista. The question is how do I solve this??? "sgl" wrote: Here goes, 1 - This is a new machine Lenovo T500 which came with Vista Business preloaded. Also Office 2007 was preloaded. I uninstalled this as I was developing in Excel 2003 and there were conflicts between the two versions especially with Outlook. Whether all files have been deleted from system cannot check. I get a lot of crashes with Excel 2003/Vista which I do not get using Windows XP Pro. 2 - Need to check this as but do not have another PC available 3 - Checked this is certainly an Excel 2003 version 4 - Same problem as before 5 - Excel shortcut leads to MS Office Excel 2003 6 - Only one version there 7 - Extension is .xls 8 - Only files in this folder are Personal.xls - checked that and nothing there thank you/sgl "joel" wrote: I need more info. 1) When did the problem start occuring. After Vista was installed. 2) Does the problem happen with more than one PC? 3) Verify you are still running with Excel 2003 using menu Help Info 4) Try opening Excel from Start Menu. does same thing happen? 5) check where the excel short cut is getting the executable. right click shortcut and check properties 6) Do a seach on your C: drive for exce.exe and see if you have more then one verion of the exe. Try opening each version ans see if the same thing happens 7) Make sure tthe extension of the file is XLS. 8) Make sure there arre no macros in your XLSTART directory that is causing the problem. these are some quick items to check to help locate the problem. I checked the Tools - Options for some setting that may explain your problems but didn't find any. "sgl" wrote: No this is a straight .xls file. I have opened an old xls workbook where a template had been developed in Excel 2000 and tried to enter data in the date/time formatted cells and this problem came to light. Everything was converting to text either when I enetered data in blank new cells or altering already completed cells. I then tested this in a brand new workbook formatting new cells as I have explained and the same thing happened. Totally confused I am!!!! "joel" wrote: Did you save the file as CSV? CSF doesn't support formating or formulas. A CSV file is really a text file that you can view with a text ditor like notepad. "sgl" wrote: Hi all, I am using Excel 2003 with Vista Business. International settings and date/time format set to United States. I am setting a "custom format" in a cell as dd mmm yy hh:mm. When the cell is empty the Type Function evaluates to 1 (numeric). I enter a date and time say 1/1/08 23:45 and this appears in the cell in exactly the same manner. The Type Function changes automatically and now evaluates to 2 as Text. If I enter a date without a time say 1/1/08 this appears as 1 Jan 08 00:00:00 (using same format as before dd mmm yy hh:mm). In the formula bar it appears as 01 01 2008 12:00:00 AM. I have workbooks developed in Excel 2000 with Windows XP which were working fine. Now with the same WkBks, but using Excel 2003, all of a sudden nothing with date/time in the same cell works. All formats have converted to Text. calculations cannot be performed. Am I missing something here which has changed between systems?? Thanks in advance/sgl |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Date / Time problem | Excel Discussion (Misc queries) | |||
Having a problem formatting short time | New Users to Excel | |||
Time Formatting Problem | Excel Discussion (Misc queries) | |||
Problem with formatting time | Excel Discussion (Misc queries) | |||
Ugh..another time and date problem...HELP | Excel Worksheet Functions |