Microsoft KB Archive/242327

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

Article ID: 242327

Article Last Modified on 1/25/2007

-

APPLIES TO


 * Microsoft Works 2000 Standard Edition
 * Microsoft Works 6.0
 * Microsoft Works Suite 2000
 * Microsoft Works Suite 2001

-



This article was previously published under Q242327



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.

Additional query words: w_works works2k w2001

Keywords: kbfaq kbhowto kbui KB242327

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.