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.