Results 1 to 2 of 2
  1. #1
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    114
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel 2007 - Bizarre uncommanded format change from number to date

    Hi There,

    I am working on an macro enabled excel spreadsheet that has numerous sheets with both number, date formatted cells. A number of the sheets also include pull down selections linking to another sheet (data vlidation). For some strange reason (I am pretty sure this happens overnight???) the majority of the numbers get converted to date fomat, ie 3 => 3 Jan 00. This is painful because I have to then go back, select the column-format-general or number...on numerous spreadsheets. Also, all the 'white space' (unoccupied cells) have also been converted to date format.

    If I make the changes, save the document (different filename), and then open it on the same day the corrected number format is maintained. However the next working day (?) I open the document and 'hey-presto' the majority of numbers are all changed to date format (this includes pull down lists and calculated numbers - typically between 1 and 200)!

    Any ideas?????

    Cheers,

    Claude

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi Claude

    This 'issue' has been raised previously with mysterious Swedish formats appearing etc - see this reply:

    Nasty problem. Seems that this can occur with large spreasheets, particularly those with pivot tables, affects all versions of Excel2003, 2007, 2010.
    Check the entries in your custom formats list: it probably has multiple repeated entries, which you can delete.

    When you copy cells from another workbook into your excel file, it seems that custom formats are possibly 'imported' too. Might be a memory issue when too many custom formats get 'imported'.

    You can 'fix' a corrupted Normal format as follows:
    Click on cell styles in the Home ribbon.
    Right click on "Normal"
    Click Modify
    Click Format
    Change format to General

    Another way would be to remove ALL custom formats from the file.
    You could use the following:

    Sub removeStyles() 'Remove all styles other than Excel's own.

    Dim st As Style

    For Each st In ThisWorkbook.Styles

    If Not st.BuiltIn Then st.Delete

    Next

    End Sub

    let us know how you get on..

    zeddy

Posting Permissions

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