Results 1 to 6 of 6
  1. #1
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    I have a date in cell W1 type in as Nov 2010 and formatted as "mmm yyyy" I want a formula in cell X1 as follows: = W1 -31 to appear as Oct 2010 and then copy the folmula from X1 to CA1, but when I insert the formula in X1 , I get ############

    I have tried this on another workbook and it work, so I am no sure what is causing this on my current workbook

    Please advise how I can resolve this
    Attached Files Attached Files

  2. #2
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    There's something really odd about this Worksheet: if you display 1 December 2010 with a number format it should show as 40,513, but yours shows as 0.

    In turn this means that X1 is a negative number -31, hence the display of ########.

    I don't think you are really displaying dates at all, which is the root cause, but I cannot see why - I'm investigating . . . .

  3. #3
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    I've at least narrowed this down to the data entry process.

    If you type 40483 (the numerical equivalent of 1 November 2010) in cell W1, cell X1 is calculated and displayed correctly.

    Now to find out what's going on with data entry . . .

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    You don't have a number in the cells, they are the literal text "Nov 2011" which is evaluated in the formula W1-31 as 0-31 which is negative so the formatted cell as a date will be negative and can not be displayed properly.

    Steve

  5. #5
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    Found it !

    You have the transition options set, which means that 1/12/2010 is treated as a division and is therefore nearly zero !

    Go into Tools, Options, Transition and, ander Sheet Options, uncheck the two checked boxes, and all will be well.

  6. #6
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi Guys

    Thanks for the replies and for the assistance. I managed to determine what was causing the date not to be recognised as a serial date. The workbook used to be a Lotus file which I converted into Excel several years ago. Under Excel uptions (Advanced) I unchecked "Transition Formula Entry"

    Regards

    Howard

Posting Permissions

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