SkipVought
Programmer
This is a reminder that Excel CHANGES all NUMERIC data that you enter into your worksheet!
I know. This is a tabloid trick! but it's true!
This is highlights & expansion from faq68-7375.
When you see the word CONVERT it means change, but in a very specific way.
When you ENTER 10 in a cell, Excel CONVERTS it to 1010 in storage but it DISPLAYS "10". Actually you're entering "10", which has a binary values of 0011 0001 0011 0000. Excel "looks" at the characters you have entered, "analyses" it and determines that you have entered a NUMBER. Then does the conversion of 0011 0001 0011 0000 to 1010. You don't need to remember all this, just that Excel changes stuff all the time and this is an oh hum.
But there is something important here. Have you ever tried to SORT a column that contained NUMBERS and TEXT? Try it some time. Excel will ask you how you want the NUMBERS sorted: A or B? It because of this: 0011 0001 0011 0000 & 1010 are not the same!
Now comes the things that can bite you.
If you enter something like 1/2, what do you think happens? the result is (in the US where month comes before day in dates) January 2 of the current year. Same with 1-2. So what's happening? Excel "looks" at the characters you have entered, "analyses" it and determines that you have entered a DATE. It parses the YEAR, MONTH & DAY and converts that into a NUMBER (the number of days since December 31, 1899) to store and displays that number in a DATE Number Format.
Now if you REALLY wanted to enter the TEXT, 1/2 or 1-2 and have Excel NOT change it, here's what you must do:
1) Change the Number Format of the cell to TEXT
2) Enter the data
If you enter the data first and then change the Number Format to TEXT, NOTHING HAPPENS! You have lost your 1/2 data! Excel has already converted it to a Date Serial Number. Of course there is UNDO, unless you turned that off.
Remember this: [highlight #FCE94F]Changing the Number Format, changes NOTHING IN THE UNDERLYING DATA![/highlight] The Number Format only affects what is displayed. It does not change the data in the cell. If you enter 1, and then change the Number Format to DATE, the DISPLAY is Jan 1 1900 but the value in the cell is still 1!
Read faq68-7375 to get the rest.
There are also two other FAQ links at the end of the blurb that you might find enlightening.
I know. This is a tabloid trick! but it's true!
This is highlights & expansion from faq68-7375.
When you see the word CONVERT it means change, but in a very specific way.
When you ENTER 10 in a cell, Excel CONVERTS it to 1010 in storage but it DISPLAYS "10". Actually you're entering "10", which has a binary values of 0011 0001 0011 0000. Excel "looks" at the characters you have entered, "analyses" it and determines that you have entered a NUMBER. Then does the conversion of 0011 0001 0011 0000 to 1010. You don't need to remember all this, just that Excel changes stuff all the time and this is an oh hum.
But there is something important here. Have you ever tried to SORT a column that contained NUMBERS and TEXT? Try it some time. Excel will ask you how you want the NUMBERS sorted: A or B? It because of this: 0011 0001 0011 0000 & 1010 are not the same!
Now comes the things that can bite you.
If you enter something like 1/2, what do you think happens? the result is (in the US where month comes before day in dates) January 2 of the current year. Same with 1-2. So what's happening? Excel "looks" at the characters you have entered, "analyses" it and determines that you have entered a DATE. It parses the YEAR, MONTH & DAY and converts that into a NUMBER (the number of days since December 31, 1899) to store and displays that number in a DATE Number Format.
Now if you REALLY wanted to enter the TEXT, 1/2 or 1-2 and have Excel NOT change it, here's what you must do:
1) Change the Number Format of the cell to TEXT
2) Enter the data
If you enter the data first and then change the Number Format to TEXT, NOTHING HAPPENS! You have lost your 1/2 data! Excel has already converted it to a Date Serial Number. Of course there is UNDO, unless you turned that off.
Remember this: [highlight #FCE94F]Changing the Number Format, changes NOTHING IN THE UNDERLYING DATA![/highlight] The Number Format only affects what is displayed. It does not change the data in the cell. If you enter 1, and then change the Number Format to DATE, the DISPLAY is Jan 1 1900 but the value in the cell is still 1!
Read faq68-7375 to get the rest.
There are also two other FAQ links at the end of the blurb that you might find enlightening.
