Tech

Guides

Business Applications ▪ Database Management ▪ Disaster Recovery ▪ Enterprise Servers & Storage ▪ IT Security ▪ Java ▪ Microsoft Office Suite ▪ Network Admin ▪ Open Source ▪ SMB ▪ SOA ▪ Tech Management ▪ Software/Web Development ▪ Windows Server ▪ Wireless Tech

Microsoft Office Suite TechGuides

Prevent Excel from turning fractions into dates

By Mary Ann Richardson, Special to ZDNet Asia
Thursday, March 13, 2008 02:12 PM

If the user wants Excel to recognize cell entries as fractions, it is necessary to apply the appropriate fraction format to the cell before entering the data.

Microsoft Excel

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.

So, 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:

Select the column that will contain the fractional data.
Right-click the selection.
Select Format Cells.
In the Number tab, under Category, select Fraction.
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 to 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.

Microsoft Access

Have Access easily select data for overtyping

Your Access forms will be easier to use if users can simply type over existing data without having to select it first. By adding a short procedure to each text box control's OnClick event to select the data automatically, users will not have to take time to click and drag over the existing data.

For example, to change the OnClick event for the First Name text box control in your Customer Data form, follow these steps:

Open the form in design view.
Right-click the First Name text box control and then select Properties.
In the Event tab, click the Build button in the OnClick property box.
Click Code and then OK.
Enter the following code at the prompt:
SendKeys "{Home}"
SendKeys "+{End}"
Press [Alt]Q.

Now, to update data in the First Name field, simply click inside the text box. Access highlights the entire entry so you can easily type over the existing text.

Microsoft Word

Let Word 2003/2002 AutoText speed up your e-mail responses

If you find yourself copying the same paragraphs into your e-mail responses--for example, the same words to send out responses to confirm a customer's appointment--you don't have to compose each e-mail reply individually or copy and paste text from a previous e-mail. Here's how to create an AutoText button in Word that will insert the text you need into your e-mail message automatically--all you need do is customize it and send.

Follow these steps:

Open the last e-mail you sent confirming a customer appointment.
Select and copy the text that you would like to use in subsequent e-mail messages.
Open a blank document in Word.
Paste the text you copied to the Word document.
Select the text you copied.
Go to Insert | AutoText, then click New.
Click in the Please Name Your Autotext Entry text box and enter Appointment Confirmation. Click OK.
Go to Tools | Customize.
Click the Toolbars tab and then click the New button.
In the Toolbar Name text box, type Client E-mails and then click OK.
In the Customize dialog box, click the Commands tab.
Scroll through the Categories list, then click New Menu.
Scroll through the Commands list and click and drag New Menu onto the Client E-mails toolbar.
Right-click New Menu in the Client E-mails toolbar.
Click in the Name area and type E-mail AutoText.
In the Customize dialog box, in the Categories list, click AutoText.
Click in the Commands list and click and drag the Appointment Confirmation entry onto the E-mail AutoText menu.
Close the dialog box.

To include the text in your message, rather than send it as an attachment, change Word Options as follows:

Go to Tools | Options.
On the General tab, clear the Mail As Attachment check box.

Now, to create your confirmation e-mail, follow these steps:

Open a new Word document.
Click the Appointment Confirmation menu in the Client E-mails toolbar.
Make any other changes to the document.
Go to File | Send To: Mail Recipient.
A new e-mail window will open with the text of your document in the message. Fill out the rest of the e-mail, then click Send.