Results 1 to 15 of 15
  1. #1
    2 Star Lounger
    Join Date
    Jan 2007
    Location
    Gray, Louisiana, USA
    Posts
    289
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Cell format (2003)

    I have a work book with two sheets. On one sheet I can't format a date - The serial number comes up. The other sheet in the same workbook formats fine. There is no protection, no conditional formatting-any idea why this is happening? Thanks

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Cell format (2003)

    Do you mean that selecting Format | Cells, then specifying a date format doesn't have any effect?

  3. #3
    2 Star Lounger
    Join Date
    Jan 2007
    Location
    Gray, Louisiana, USA
    Posts
    289
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cell format (2003)

    Correct-If I click in yhe cell I see the date in yhe formula bar, i.e. 1/30/07 but in the cell it is 39112

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Cell format (2003)

    Could you post a small demo workbook?

  5. #5
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Cell format (2003)

    Hi Stans

    Highlight one of the cells with the number in it and see if it is a number stored as text. If you highlightit and then look in the Formula bar is there an inverted comma in front of the number? Like the image below. If so, try removing the inverted comma and reformatting to date/time.
    Jerry

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Cell format (2003)

    Hi,
    Try this:
    1. Select that sheet.
    2. Select Tools-Options from the menu, and on the View tab uncheck 'Formulas' under the Window options section.

    HTH
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Cell format (2003)

    Do you happen to have ticked Formulas in the View tab of Tools | Options? If so, turn it off.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Cell format (2003)

    Oops...

  9. #9
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Cell format (2003)

    <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Regards,
    Rory

    Microsoft MVP - Excel

  10. #10
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Cell format (2003)

    Rory, do they happen to have ticked Formulas in the View tab of Tools | Options? If so, they should turn it off. <img src=/S/grin.gif border=0 alt=grin width=15 height=15> <img src=/S/rofl.gif border=0 alt=rofl width=15 height=15> <img src=/S/innocent.gif border=0 alt=innocent width=20 height=20>
    Jerry

  11. #11
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Cell format (2003)

    Of course, we'll all look pretty stupid if it's not that! <img src=/S/yep.gif border=0 alt=yep width=15 height=15>
    Regards,
    Rory

    Microsoft MVP - Excel

  12. #12
    2 Star Lounger
    Join Date
    Jan 2007
    Location
    Gray, Louisiana, USA
    Posts
    289
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cell format (2003)

    That was the problem - My formulas were ticked. I calmed them down and everything is fine now... <img src=/S/rofl.gif border=0 alt=rofl width=15 height=15> Thanks to everyone.

  13. #13
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Cell format (2003)

    Excellent, it appears that Rory and Hans realised it together, I couldn't resist a Jezza ribbing <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Jerry

  14. #14
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Cell format (2003)

    You may laugh now, but soon you will be assimilated into the hive mind of the Lounge... <img src=/S/alien.gif border=0 alt=alien width=14 height=15> <img src=/S/newbrain.gif border=0 alt=newbrain width=21 height=22> <img src=/S/brainwash.gif border=0 alt=brainwash width=15 height=15>
    Regards,
    Rory

    Microsoft MVP - Excel

  15. #15
    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

    Re: Cell format (2003)

    Resistance is Futile....

    Steve

Posting Permissions

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