Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Jul 2004
    Location
    Sumner, Washington, USA
    Posts
    112
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Date Formatting Doesn't 'Stick' (Excel XP)

    I am attaching a worksheet that has two cells containing dates and three formulas that use those dates in a calculation (highlighted in yellow). Both work fine the way they are. However, if I change the date, Excel seems to be converting the cell to a different data type (or something) and the calculations don't work any more. If I explicitly set the cell to a date, like is done in the first cell on the sample, I can change the date to a different day and the formulas recalculate fine. But if I change the date in the second cell to a new date by just typing in a new date, it results in an error.

    It seems like this must be one of those strange data-formatting things that happen in Excel sometimes, like when a series of numbers are imported as text and you have to fiddle around to get them back to numbers again. But I can't figure out how to fix this one. Thanks!

    --Karyl

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Date Formatting Doesn't 'Stick' (Excel XP)

    Anytime that the entry in cell B2 results in negative days remaining, your formula in cell B3 errors. Any date prior to today results in negative days remaining. Is that what you mean? To correct this you may have to fix your formulas in Cell B4. I can't fix it because I'm not sure of the logic to determine the salary esc(alation?) date.

    If that's not it can you be more specific?
    -John ... I float in liquid gardens
    UTC -7DS

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Formatting Doesn't 'Stick' (Excel XP)

    Which cell is the "second cell"? What date are you typing into the cell. What is the format of the date you are typing into the cell. On the Windows Regional Options control panel, what is the short date format, and what is the date separator character.

    I don't have any problems typind dates into either cell B1 or B2, so I am guessing that you are entering something that Excel does not recognize as a date and it placing it into the cell as a text string. That would cause your formulas to break.
    Legare Coleman

  4. #4
    2 Star Lounger
    Join Date
    Jul 2004
    Location
    Sumner, Washington, USA
    Posts
    112
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Formatting Doesn't 'Stick' (Excel XP)

    I'll have to fix the formula in Cell B3, but that's not the problem. When I change the date in cell B1 or B2, it changes the cell from a date to something else, as near as I can tell. If I change 5/1/2005 in cell B2 to 5/2/2005, it breaks the calculation in cell C2. If I change it back to 5/1/2005, it stays broken. The only way I can get it back is via Undo.

    After reading that two users here were able to enter dates without a problem, I pulled up the sample sheet I sent on my home computer. No surprise, I don't have the same problem here, either. I can change the dates without any errors (well, except for the salary escalation date calculation, but that's my error). But the other computer still demonstrates the same error. Both computers are running Office XP, but my work computer has Office 2003 installed as well. When I opened the sheet in 2003, it was still broken.

    I checked the regional settings for dates, and they are the same on both computers. Another piece of information that may help: I'm pretty sure that when I was working on this a couple weeks ago, it was working correctly. I tried a variety of dates while checking the salary escalation date formula (which is pretty close to right, I think!). Anyway, I would have noticed if it weren't working then. So, what could have happened in the meantime that would impact this? I did copy all of the sheets in the original workbook to a new one, to fix another error I was getting from what seems to have been a corrupt user form. Any ideas?

    --Karyl

  5. #5
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Formatting Doesn't 'Stick' (Excel XP)

    Could the version of the workbook on your computer have become corrupted? HTH
    Gre

  6. #6
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Date Formatting Doesn't 'Stick' (Excel XP)

    Karyl, I didn't have any problem in XP or 2003 entering a date in cell B2. The worksheet as I d/l'd it from the Lounge shows no sign of corruption. If you haven't in the meantime, maybe you could post a small screenshot example?

    However, I did find that what I stated above about negative time remaining blowing up the formula in B3 is not correct; it's more like any date in Cell B2 that is before April 1 of the year following the year in cell B1 will cause an error in the B3 formula. I attempted to edit my post about 14 hours ago but my Lounge access has been intermittent.

    One minor detail that I noted is that Cell B1 uses the format "*m/d/yyyy", and the other two use "m/d/yyyy", the difference being that the former will reflect you locale settings from the control panel, but I don't see that it should matter.
    -John ... I float in liquid gardens
    UTC -7DS

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Formatting Doesn't 'Stick' (Excel XP)

    On the sheet that does not work, check to see if someone has changed the cell format for those two cells to Text. Actually, select the two cells and change the format to General or to a Date format. It sounds like the cells have a date in them, but the cell is formatted as Text (changing the format to Text after the date was entered would not change the date to text).
    Legare Coleman

  8. #8
    2 Star Lounger
    Join Date
    Jul 2004
    Location
    Sumner, Washington, USA
    Posts
    112
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Formatting Doesn't 'Stick' (Excel XP)

    Thanks to everyone for testing this out for me. When I saw that no one else was having trouble, I downloaded the file to my personal laptop, and it works just fine there. So it looks like I've got bigger problems. Neither version of Excel (XP or 2003) works correctly on my work laptop. I thought, as you suggested, that maybe the file on the laptop was corrupted, but I downloaded the file from here that is working fine for everyone, and it doesn't work, either.

    So, I tried creating a new file with calculated date fields, but I didn't get anywhere. Next, I opened one of my "reference" Excel sheets that just has a bunch of different common date calculations, and I tried changing one of the date cells in that document. Same problem. The existing date in the cell is fine, but as soon as I enter a different date, the format changes and all the calculations blow up.

    So, I checked Word, and date calculations within fields in Word documents seem to be fine. Which doesn't tell me much of anything except that it is probably Excel-specific and not a Windows date setting gone awry. So I just called my friendly IT support guy (I'm working at home today), and he is going to work on repairing or reinstalling or whatever it takes to get this fixed tomorrow. As I said, I have both Office XP and Office 2003 with all the XP Developer's stuff and the Visual Basic .NET Tools for 2003 plus .NET Enterprise, and I'm not sure how just removing and reinstalling the Office versions will impact all of that. I know it was a pain to get it all on this system in the first place and had to be done sequentially. Hopefully, he can just repair components without the need to start from scratch.

    --Karyl

  9. #9
    2 Star Lounger
    Join Date
    Jul 2004
    Location
    Sumner, Washington, USA
    Posts
    112
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Formatting Doesn't 'Stick' (Excel XP)

    Update:

    Well, this really turned out to be one of those "Now don't you feel stupid?" situations! Several weeks ago, I was working with the word processors and discussing with them the use of the optional break character after a slash (so long URL's would automatically wrap, for example). In preparation for that discussion, I'd been playing around with it in Word and had tried setting it up as an AutoCorrect entry. Ah, I see you've figured it out! The AutoCorrect entry was still active, of course, and was changing any slashes I entered in Excel into slash-invisible character entries, and, consequently, Excel was saying, "Oh, you've changed this field to text!" The IT guy helping me troubleshoot tried entering the date with hyphens instead of slashes, and when that worked, the light dawned! I guess I hadn't worked with any of the date fields in the interim, so it hadn't shown up until now.

    Again, thanks for all your help. Some of you noticed that the other date calculation WAS going wacky if you ended up with negative days, and that needs to be fixed. If you want to take a crack at fixing the formula, I'd appreciate the input. I'm sure I have it set up in a totally convoluted way, both because I kept adding things to it as I went along and because I'm still really new at all the Excel formula stuff and don't know enough to figure out the easy way to do something. The cell should default to the first "April 1" date after the Start Date but be blank if there are no April 1's between the start and end dates. Like this:

    Start Date: March 1, 2004
    End Date: May 1, 2006
    Sal.Esc.Date: April 1, 2004

    Start Date: May 1, 2004
    End Date: November 20, 2004
    Sal.Esc.Date: BLANK

    Thanks!

    --Karyl

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •