Works: Combining Month and Year to Make a Date (126047)


The information in this article applies to:

  • Microsoft Works 3.0
  • Microsoft Works 3.0a
  • Microsoft Works 3.0b
  • Microsoft Works 4.5
  • Microsoft Works 4.5a
  • Microsoft Works for Windows 95, version 4.0
  • Microsoft Works for Windows 95, version 4.0 4.0a
This article was previously published under Q126047

SUMMARY

If a date has been divided into months, days, and years in separate fields in a database (or columns in a spreadsheet), it can be recombined into one date entry. However, normal concatenation cannot be used because the years and the days are stored as numbers, and the months are stored as the serial number of the first day of that month in the current year. Use the formulas below to combine the date.

MORE INFORMATION

NOTE: Below are formula samples. The formulas should be entered all on one line, in one field. The formulas may also be used to convert separated dates in a spreadsheet by replacing the field names with the appropriate cell references. Each of these formulas produces a serial number that can be translated into the correct date by formatting the field or cell to a date.

If the date has been divided into separate month (spelled out), day, and year fields, the following formula should be used

=VALUE(STRING(MONTH(<Fieldname1>),0)&"/"&STRING(<Fieldname2>,0)&"/"& STRING(<Fieldname3>,0))

where <fieldname1> contains the month spelled out, <fieldname2> contains the day, and <fieldname3> contains the year. If <fieldname1> contains the month as a number, such as a 2 for February, the following formula can be used:

=VALUE(STRING(<Fieldname1>,0)&"/"&STRING(<Fieldname2>,0)&"/"& STRING(<Fieldname3>,0))

If the date has been divided with the month and day in one field and the year in another, use the following formula

=VALUE(STRING(MONTH(<Fieldname1>),0)&"/"&STRING(DAY(<Fieldname1>),0) &"/"& STRING(<Fieldname2>,0))

where <fieldname1> contains the separated month and day, and <fieldname2> contains the year.

If the date has been divided with the day in one field and the month and year in another, use the following formula

=VALUE(STRING(MONTH(<Fieldname1>),0)&"/"&STRING(<Fieldname2>,0)&"/"& STRING(YEAR(<Fieldname1>),0))

where <fieldname1> contains the separated month and year, and <fieldname2> contains the day.

For more information about this topic, please see the following article in the Microsoft Knowledge Base:

123361 Works: Spreadsheet or Database Returns ERR Using the & Operator

REFERENCES

For more information about how to perform this task in Works, see your Works printed documentation or online Help.
Modification Type:MajorLast Reviewed:11/15/2004
Keywords:kbinfo KB126047
©2004 Microsoft Corporation. All rights reserved.