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

Article translations Article translations
Article ID: 242327 - View products that this article applies to.
This article was previously published under Q242327
This article has been archived. It is offered "as is" and will no longer be updated.
Expand all | Collapse all

On This Page

Summary

This article describes how to create either a date cell in a spreadsheet or a date field in a database that combines the data contained in separate day, 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 into a single cell or field, use the appropriate method below. You must manually enter the formula you use in the date cell or field. If you cut and paste a formula into the date cell or field, you may receive an error message. The best method is to type the functions and enter the cell or field names, such as "field1" or "field2," by clicking on the actual cell or field; when you click the cell or field, its name is automatically entered into the formula.

Month + Day + Year

If one cell or field contains the name of a month, another the day of the month, and another the year, type the following formula in the date cell or field:
=VALUE(STRING('field1',0)&"/"&STRING('field2',0)&"/"& STRING('field3',0))
where 'field1' contains the number of the month, '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, and field2 contains the day.

NOTE: You must type the single quotation marks around the cell or 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.

Properties

Article ID: 242327 - Last Review: October 26, 2013 - Revision: 4.0
Applies to
  • Microsoft Works 6.0
  • Microsoft Works Suite 2000
  • Microsoft Works Suite 2001
Keywords: 
kbnosurvey kbarchive kbhowto kbui KB242327

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com