Page 1 of 3 123 LastLast
Results 1 to 10 of 23

how to turn off excel's auto formatting of data.

This is a discussion on how to turn off excel's auto formatting of data. within the Excel Questions forums, part of the Question Forums category; hello, i often have the most annoying problem with excel. it automatically decides how to format specific data. here's one ...

  1. #1
    New Member
    Join Date
    Mar 2003
    Posts
    2

    Default how to turn off excel's auto formatting of data.

    hello,

    i often have the most annoying problem with excel. it automatically
    decides how to format specific data.

    here's one example. i've imported a text file with some timing information in minutes:seconds. every time excel automatically makes it
    hours:minutes. if i go and format the information to minutes:seconds,
    the data is then show as incorrect.

    here's another example. the data is 28:17 (28 minutes, 17 seconds). well, when i import it, the data is now conveted to "01-01-1900 04:17:00". even when i try to copy the format of another cell, it then changes the data to 04:17:00. argh!!! why on earth does excel change 28:17 to a date and a time in 1900?

    another classic one is if you type 05-09, excel will change it to 05-sep. as if the information is a date in -- 05 september 2003. it's so irritating that it does this.

    what causes this to happen? why does it automatically decide how to
    format the information? whatever it is i would like to turn it off so
    that excel never formats any data until i tell it to.

    any help would be appreciated.
    bruno

  2. #2
    MrExcel MVP
    Join Date
    May 2002
    Posts
    13,993

    Default

    Hi - welcome to the board!

    Why not just format the cells in question as you want them before you construct the formulas / inport the data?

  3. #3
    New Member
    Join Date
    Mar 2003
    Posts
    2

    Default

    hello again,

    formatting the cells beforehand is one way of approaching the problem, but it often doesn't help. 28:17 still becomes "01-01-1900 04:17:00" even if i format the cells to be text cells before entering the data. what on earth is causing excel to handle this hours:minutes data as a date & hours data?

    anyway, does that sound a bit illogical reasoning with excel? you have to format the cells before you enter the data so excel doesn't automatically format the data for you. shouldn't it be the other way around? -- the data in the cells is NOT formatted automatically by excel but is determined by the user. also, it is often that one cannot foresee how the cells will be used in order to format before entering text.

    isn't there some function in the options section that turns the autoformatting off? word has an autoformatting window where all its annoying autoformatting is de-selected.

    bruno

  4. #4
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    69,411

    Default

    Excel interprets 28:17 as 28 hours and 17 minutes. As a date serial number this is 1.178472 - that's one day and 4hours 17 minutes. Day one is 1st Jan 1900, so that's what you get.

    You can avoid the autoformatting by clicking the column(s) and checking Text as the Column data format at Step 3 of the Text Import Wizard.

  5. #5
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654

    Default

    bruno, Excel's treatment of time values is explained in the Excel Help topic for "Tips on entering dates and times"...

    Microsoft Excel treats dates and times as numbers. The way that a time or date is displayed on a worksheet depends on the number format applied to the cell. When you type a date or time that Excel recognizes, the cell's format changes from the General number format to a built-in date or time format. By default, dates and times are right-aligned in a cell. If Excel cannot recognize the date or time format, the date or time is entered as text, which is left-aligned in the cell.

  6. #6
    New Member
    Join Date
    Mar 2009
    Posts
    1

    Default Re: how to turn off excel's auto formatting of data.

    This (meaning entering numbers in format NNNN:NN) can be a pain because once Excel has formatted the actual VALUE in the cell has been altered. If you format back to text you may end up with a number that was not what you entered.

    The following VBA function should convert the altered value back to it's original.

    Function GetTextBackFromDate(oCell As Range) As String

    Dim v_Value As String
    Dim v_Number As Double
    Dim v_Days As Integer
    Dim v_DayHours As Integer


    Dim v_PartialDays As Double
    Dim v_PartialDayHours As Integer
    Dim v_PartialHours As Double
    Dim v_Minutes As Double
    Dim v_IntMins As Integer


    Dim v_TotalHours As Integer


    On Error GoTo EH:

    v_Value = oCell.Value

    v_Number = CDbl(v_Value)
    v_Days = Round(v_Number)
    v_DayHours = v_Days * 24

    v_PartialDays = v_Number - v_Days
    v_PartialDayHours = Round((v_PartialDays * 24), 0)

    v_PartialHours = (v_PartialDays * 24) - v_PartialDayHours
    v_Minutes = Round(v_PartialHours * 60, 0)

    v_TotalHours = v_DayHours + v_PartialDayHours

    GetTextBackFromDate = CStr(v_TotalHours) & ":" & CStr(v_Minutes)

    Exit Function
    EH:
    GetTextBackFromDate = "Error"

    End Function

  7. #7
    New Member
    Join Date
    Mar 2010
    Posts
    1

    Default Re: how to turn off excel's auto formatting of data.

    I am having trouble with this same problem. I don't understand how to apply the code to my problem. I have column A full of data from an imported source. Data is not dated but uses a ":" to seperate a 4 or 5 digit number from a 1 to 4 digit number when ever the second part of the number xxxx:yy is below 61 it thinks It is a time/date. Any help on this is greatly appreciated.

  8. #8
    New Member
    Join Date
    May 2010
    Posts
    2

    Default Re: how to turn off excel's auto formatting of data.

    My solution follows: I'm using Excel X for Mac. I have to import large masses of hyphenated data for mapping purposes (usually designating particular coordinates), usually from .txt files imported from my GPS. The auto date drives me crazy, especially since I often have to import dozens or hundreds of coordinates that I can't change manually. I couldn't find any clear or specific answers online, but this worked for me: File>Import>Text Import Wizard Step 3> select the appropriate data column, then the TEXT radio button, then FINISH. Everything in the column should import without change. I'm not sure if this will carry over into the Windows environment, but it might be worth a shot.

  9. #9
    New Member
    Join Date
    May 2010
    Posts
    2

    Default Re: how to turn off excel's auto formatting of data.

    Just a quick clarification of my earlier instructions. In the command sequence, "File>Import" should be "File>Open". The sequence should thus be File>Open>Text Import Wizard Step 3 of 3>select the appropriate data column, then the TEXT radio button, then FINISH. Sorry if this caused any confusion.

  10. #10
    New Member
    Join Date
    Jul 2011
    Posts
    1

    Thumbs up Re: how to turn off excel's auto formatting of data.

    Great advice. Converting to text in step 3 of import wizard works very well.

    Thanks.

Page 1 of 3 123 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com