Date/Time:
-
Excel looks at the character string that you entered and guesses that it’s a Date or Time by detecting certain patterns (m/d/yy or m-d-yyyy for instance). You’ve probably figured this out a long time ago.
This can be a blessing or it can be frustrating if you are really NOT entering a Date or Time. -
Using your Regional Settings for Date & Time, Excel parses the CHARACTERS you entered into year, month, day, hour, minute, second. Excel makes other assumptions if you do not enter a complete Date/Time string such as 3/2. For instance, in the USA, Excel assumes that 3/2 is month 3, day 2, current year and 17: is assumed to be 5:00:00 PM.
-
Using the parsed values, Excel CONVERTS the values to a Date/Time Serial Value. For instance, if I entered 2/3 18: and this occurred in 2020, Excel converts this to 43864.75 which can be FORMATTED 2/3/2020 18:00.
Scientific Notation:
-
Excel looks at the string that you entered and guesses that it’s Scientific Notation if the pattern is aEb. This can be a blessing or it can be frustrating if you are really NOT entering a number, like some sort of identification code (I have had Machine IDs like 171E2 and THAT string value is what I want!).
-
Using the rule that Scientific Notation is based on a × 10^b where the exponent b is an integer, and the coefficient a is any real number, Excel takes the CHARACTERS that you entered, parses the exponent and coeficient, and CONVERTS these values into a floating point number.
In ALL of these instances, Excel actually CHANGES the entered value to something entirely different. So if you want to insure that you data is NOT converted, you must do one of several things:
-
Format the cells as TEXT, BEFORE you enter any data.
Once you enter your data and Excel changes it as described above, formatting does nothing to bring back the original data. Recall is your only hope. -
Enter values that you want to be stored as TEXT, with a leading APOSTROPHY. Excel interprets a leading APOSTROPHY as an identifier for a LABEL or TEXT.
-
If you get data in a text file, like a .csv, DO NOT OPEN THESE FILES WITH EXCEL!!!
Rather, in an new or existing Workbook, use Data > Get External Data > IMPORT Text File… where you can EXPLICITLY define the Data Type in any
-columns having DATE/TIME values as DMY/MDY/YMD
-columns having numeric IDENTIFIER values as TEXT.
Remember this rule of thumb:
Formatting changes NOTHING!
Any underlying data remains UNCHANGED!
Check out Understanding Dates and Times & why they seem to be so much trouble?