KB147172 - Works: Wrong Date Separator Causes"ERR" in Calculated Field

Article ID: 147172

Article Last Modified on 11/24/2003

Microsoft Works 4.0 Standard Edition
Microsoft Works 2.0 Standard Edition
Microsoft Works 2.0a
Microsoft Works 3.0 Standard Edition
Microsoft Works 3.0 Multimedia Edition
Microsoft Works 3.0a
Microsoft Works 3.0b

This article was previously published under Q147172

SUMMARY

If a database field or spreadsheet cell contains a formula that refers to another field or cell containing a date, the formula may return an incorrect result. This can occur if the date was not entered in the format specified in the Windows Control Panel.

For example, suppose you have a database in which one field is called "Date Entered" and a second field is called "Year of Sale." The formula in Year of Sale is:

   =Year(Date Entered)

and the value in Date Entered is 2-7-96. If your default date separator is a "/", then ERR is displayed in the "Year of Sale" field for that record.

MORE INFORMATION

Works expects dates to be entered in a specific format that is determined by the settings in the Windows Control Panel.

For example, if the date separator in the Control Panel is set to a forward slash (/), and you enter a date with a hyphen (-), Works considers the date as text and a formula that expects a number returns incorrect results.

Changing Date Separator

To change the Date Separator, use the steps below for your version of Windows.

Windows 95

Click the Start button, point to Settings, and then click Control Panel.
Double-click the Regional Settings icon.
On the Date tab, change the Date separator setting, and then click OK.

Windows 3.x

Double-click the Control Panel icon, usually located in the Main group of Program Manager.
Double-click the International icon. An example of the current option for entering a date in both short and long form is shown in the Date Format section.
To change the default date format, click the Change button in the Date Format Section.

For additional information about date formats in the Spreadsheet and Database, please see the following article in the Microsoft Knowledge Base:

124292 Works: Dates Displayed as Text in Spreadsheet and Database

For additional information on converting text in the spreadsheet and database into numbers, please see the following article in the Microsoft Knowledge Base:

102421 Works: Converting Numbers Entered as Text into a Numeric Format

Additional query words: w_works 2.00 2.00a 2.00b 3.00 3.00a 3.00b 4.00 works95 separater forms calculates

Keywords: kbinfo KB147172