Prevent Excel from turning fractions into dates

If you do not apply a fraction format to a cell before you enter a fraction into it, Excel will convert the fraction into a date. For example, if you enter 1/50 into a cell, Excel immediately converts it to Jan-50. If you then try to reformat the same cell to a fraction, Excel converts it to 18264, the internal number that represents Jan-50. In that case, if you want Excel to recognize your cell entries as fractions, you must apply the appropriate fraction format to the cell before you enter the data.

For example, say you are importing a column of data from an Access database table that contains fractions such as 1/50, 2/70, and 30/65. You will need to format the column of cells containing the data to an appropriate fraction format before importing. Follow these steps:

  1. Select the column that will contain the fractional data.
  2. Right-click the selection.
  3. Select Format Cells.
  4. In the Number tab, under Category, select Fraction.
  5. Under Type, select Up To Two Digits (21/25), then click OK.

Now, when you import the data to that column, Excel will format each entry as a fraction. Keep in mind that if the data contained fractions such as 30/750, you would select Up To Three Digits (312/943) in Step 5. If you wanted the display your data as fractions over 100 (for example, 30/65 displayed as 46/100), you would select As Hundreds (30/100) in Step 5.

Alternately, if you won’t be using the fractions in calculations, you can prevent Excel from changing your entries to dates by applying the Text format to the cell before you enter the data. In that case, in Step 4 select Text under the Category list, and then click OK.

<p><b>Miss an Excel tip?</b></p>

<p>Check out the <a href=”http://techrepublic.com.com/1200-10877-5735748.html”>Microsoft Excel archive</a>, and catch up on other Excel tips.</p>

<p><i>Help users increase productivity by <a href=’http://nl.com.com/servlet/one_click_signup?email=$customer.emailAddress&list_id=e056&id=$customer.customerId&tag=nl.MONTH.E-CODE’>automatically signing up</a> for TechRepublic’s free Microsoft Office Suite newsletter, featuring Word, Excel, and Access tips, delivered each Wednesday.</i></p>

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

Up ↑

%d bloggers like this: