 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / December 2006date formating will not stick
I'm using Office97. This problem has not been an issue until recently. In the past (unless I'm crazy), when I formatted a cell, perhaps in the format MM/DD/YY, the value was always displayed in that format even if you entered new values in the format mm/d/yyyy, or any other format. Now, on all my new spreadsheets, when I enter a date, the format changes to mm/dd/yyyy independent of the prior setting. I assume I changed some default format setting somewhere. Can someone tell me what I may have changed? Hi, Use the Value function to get the values of all the cells in another column and then Format it by Date and it will work. It seems you have Dates stored as strings in the cells. Regards Ankur www.xlmacros.com > I'm using Office97. This problem has not been an issue until recently. > In the past (unless I'm crazy), when I formatted a cell, perhaps in the [quoted text clipped - 4 lines] > default format setting somewhere. Can someone tell me what I may have > changed? It's likely that cells are formatted as text. Data>Text to Columns>Finish on the column should take care of it. > I'm using Office97. This problem has not been an issue until recently. > In the past (unless I'm crazy), when I formatted a cell, perhaps in the [quoted text clipped - 4 lines] > default format setting somewhere. Can someone tell me what I may have > changed? I'm nearly certain that the cellS are not formatted as text. If I use the contents of the cell in a formula such as z12 = C12 * 1, where the date is entered into cell C12, the contents of Z12 is a number like 36,000 or so, the date number. I do not encounter the problem on my other machine that is also running Office 97. I think machines were loaded with the same copy of office, although I have two separate legitimate licenses for Office97. I encounter the same problem on brand new workbooks opened on the same machine. SUGGESTIONS ARE STILL WELCOME. I'm going to try to move the file to the other machine, and see if I have the same problem when the file is opened with another copy of the software. WindsurferLA > It's likely that cells are formatted as text. > Data>Text to Columns>Finish on the column should take care of it. [quoted text clipped - 7 lines] >> default format setting somewhere. Can someone tell me what I may have >> changed? Saved from a previous post. When you do Format|cells|Number tab and look at that cell's format, I'm betting you see something like: *03/14/2001 That asterisk means that you chose a short date format that is picked up from the Windows Regional settings (Date tab). xl2002 is more honest with the way it deals with dates. At the bottom of that dialog (xl2002): Date formats display date and time serial numbers as date values. Except for items that have an asterisk (*), applied formats do not switch date orders with the operating system. Since yours is marked with an asterisk, your date will switch formats with the setting in the OS. ===== So if format your date using one of the formats marked with an asterisk, then excel will use that pc's windows date format setting. So if you open the file on a different pc that uses a different windows date format setting, then you'll see something different. This was what Toni.Gee tried to explain--excel uses a windows setting to know how to display those formats that are marked with an asterisk. > I'm nearly certain that the cellS are not formatted as text. If I use > the contents of the cell in a formula such as z12 = C12 * 1, where the [quoted text clipped - 25 lines] > >> default format setting somewhere. Can someone tell me what I may have > >> changed?  SignatureDave Peterson The is no asterisk in front the 04/05/97 sample date in the Format | cells | number | date where date is an option under number. If I move the file to my other machine, the cell formating works as expected. Independent of how the date is entered, the date is formatted in accordance to the date formating of the cell. I have noticed that the machine with the problem is shown as running XL97 w/SR-1, while the machine that works right shows XL97 w/SR-2. I need to see about an SR-2 update to Excel on my problem machine. WindsurferLA > Saved from a previous post. > [quoted text clipped - 55 lines] >>>> default format setting somewhere. Can someone tell me what I may have >>>> changed? I don't think that xl97 had the note at the bottom. I don't recall if that version marked the samples that are picked up from the windows regional settings. But it would be simple for you to test. Try changing that windows regional setting and see what happens to the way the date is displayed. > The is no asterisk in front the 04/05/97 sample date in the > Format | cells | number | date where date is an option under number. [quoted text clipped - 68 lines] > >>>> default format setting somewhere. Can someone tell me what I may have > >>>> changed? SignatureDave Peterson To review, all dates entered into EXCEL97 on my DELL computer insist on displaying the year in YYYY format unless subsequently manually reformatted. Dates enterred into EXCEL97 on my IBM computer, act as expected. Both machines are running WinXP-Pro SR2. The software on both machines appears to be identical except: DELL indicates " Sfwr Abstraction Layer = "5.1.2600.2705(xpsp.050622-1524)" IBM indicates " Sfwr Abstraction Layer ' "5.1.2600.2180(xpsp_Sp2_rtm o4o8o3-2158 " I've done two things: (1) I replaced all 77 MByte of C:\Program Files\Microsoft Office\Office directory files on my DELL computer with the identical directory files from IBM computer which does not have the Excel date problem. I also replaced all C:\Program Files\OfficeUpdate11 files from the IBM computer with does not have the Excell date problem. I also unsuccessfully sought to install the SR-2 Update, [sr2bof97.exe]. When I open Excel on DELL, it now indicates SR 2(l) version, rather than SR 1, but it does not simply display "SR 2" as on the IBM machine. I realize this move could create problems in that Office files may no longer agree with files anticipated by the registry, but so far I've found no additional problems. The bottom line is that the date formating problem has not changed. No mater how I format a cell, and no matter how the date is entered, it always appears with a yyyy format... But.... The format settings for the column for the month and day are observed. Thus if the column formats are set to mmm/d/yy, a date such as 1/3/05 will be immediately shown as Jan/3/2005. (2) I change the default windows Time and date setting to be of the form mmm/dd/yy. The change did not impact how excel displayed dates. (3) When I try to install pstvh sr2bof97.exe , it says it can't install, possibly because of incompatibility. I had the same problem before I copied over the files. I'm able to install path xl8p10pkg.exe which has most of the same updates. (4) I've rebooted my machine from a cold boot to make sure changes were reflected in how the program was loaded. I have hesitate to totally remove MSoffice and reinstall, because undoubtedly there are customizations in Word and Powerpoint that I'll loose. My Office install disc. does not seem to give me the option to just replace portions of OFFICE, although I seem to recall that feature. Again... this is a puzzlement, and suggestions will be welcomed. > I don't think that xl97 had the note at the bottom. I don't recall if that > version marked the samples that are picked up from the windows regional [quoted text clipped - 75 lines] >>>>>> default format setting somewhere. Can someone tell me what I may have >>>>>> changed? I don't have any more guesses. > To review, all dates entered into EXCEL97 on my DELL computer insist on > displaying the year in YYYY format unless subsequently manually [quoted text clipped - 123 lines] > >>>>>> default format setting somewhere. Can someone tell me what I may have > >>>>>> changed? SignatureDave Peterson Thanks for help... The next step is to remove MSoffice and reload it from scratch, and then install all the various updates. WindsurferLA > I don't have any more guesses. > [quoted text clipped - 125 lines] >>>>>>>> default format setting somewhere. Can someone tell me what I may have >>>>>>>> changed? |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.