Works: How to Combine the Month, Day, and Year Cells or Fields into a Date Cell or Field

This article was previously published under Q242327
This article has been archived. It is offered "as is" and will no longer be updated.
Summary
This article describes how to create either a date cell in a spreadsheetor a date field in a database that combines the data contained in separateday, month, and year cells or fields.

NOTE: In Works, you cannot use text concatenation functions to combine day, month, and year cells or fields into a date cell or field, because the data in day, month, and year cells or fields are stored as serial numbers.

To concatenate a date from separate day, month, and year data intoa single cell or field, use the appropriate method below. You mustmanually enter the formula you use in the date cell or field. If youcut and paste a formula into the date cell or field, you may receivean error message. The best method is to type the functions and enterthe cell or field names, such as "field1" or "field2," by clickingon the actual cell or field; when you click the cell or field, itsname is automatically entered into the formula.

Month + Day + Year

If one cell or field contains the name of a month, another the day ofthe month, and another the year, type the following formula in the datecell or field:
=VALUE(STRING('field1',0)&"/"&STRING('field2',0)&"/"& STRING('field3',0))
where 'field1' contains the number of themonth, 'field2' contains the day of the month,and 'field3' contains the year.

NOTE: You must type the single quotation marks around the cell or field names as shown.

Month/Day + Year

If one cell or field contains the month and day, and another the year,type the following formula in the date cell or field:
=VALUE(STRING(MONTH('field1'),0)&"/"&STRING(DAY('field1'),0) &"/"& STRING('field2',0))
where 'field1' contains the month and day, and'field2' contains the year.

NOTE: You must type the single quotation marks around the cell or field names as shown.

Day + Month/Year

If one cell or field contains the month and year, and another the day,type the following formula in the data cell or field:
=VALUE(STRING(MONTH('field1'),0)&"/"&STRING('field2',0)&"/"& STRING(YEAR('field1'),0))
where field1 contains the month and year, andfield2 contains the day.

NOTE: You must type the single quotation marks around the cellor field names as shown.
More information
After the formula is entered and a serial number appears, you can format the date cell or field with the date style you want.

To format a spreadsheet cell:
  1. Right-click the cell, and then click Format.
  2. On the Number tab, click Date under Format.
  3. Under Options, click the date style you want in the Dates box.
  4. Click OK.
To format a database field:
  1. Click the field.
  2. On the View menu, click List.
  3. On the Format menu, click Field.
  4. On the Field tab, click Date under Format.
  5. Click the date style you want in the Appearance box.
  6. Click OK.
w_works works2k w2001
Properties

Article ID: 242327 - Last Review: 10/26/2013 17:12:00 - Revision: 4.0

  • Microsoft Works 6.0
  • Microsoft Works Suite 2000
  • Microsoft Works Suite 2001
  • kbnosurvey kbarchive kbhowto kbui KB242327
Feedback