KB112429 - Works: Converting a Five- or Six-Digit Number to a Date

SUMMARY

Some database applications may require you to enter date values as a 5- or 6-digit number (that is, 5/15/92 is entered as 051592). To convert this value to a date in Works, enter the following formula in a separate field

=DATE(MOD(ref,100),INT(ref/10000),MOD(INT(ref/100),100))

where "ref" is replaced with a field name in the database or a cell reference in the spreadsheet.

This equation places the decimal equivalent of the date in the destination field. Note that if the day or year number has a single digit, as in 5/5/92, the original date value would have to include a zero, as in 050592.

To change the field to a date format in Works 3.0, 4.0, 4.5 and 4.5a:

From the Format menu, click Number.
Select Date from the Format box.
Select the desired date format in the Date box and click OK or press ENTER.

To change the field to a date format in Works 2.0:

From the Format menu, click Time/Date.
Select the desired date format in the Show and Date boxes and click OK or press ENTER.