Microsoft KB Archive/126047

= Works: Combining Month and Year to Make a Date =

Article ID: 126047

Article Last Modified on 11/15/2004

-

APPLIES TO


 * Microsoft Works 3.0 Standard Edition
 * Microsoft Works 3.0a
 * Microsoft Works 3.0b
 * Microsoft Works 4.5 Standard Edition
 * Microsoft Works 4.5a
 * Microsoft Works 4.0 Standard Edition
 * Microsoft Works 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(),0)&"/"&STRING(,0)&"/"& STRING(,0))

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

=VALUE(STRING(,0)&"/"&STRING(,0)&"/"& STRING(,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(),0)&"/"&STRING(DAY(),0) &"/"& STRING(,0))

where contains the separated month and day, and 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(),0)&"/"&STRING(,0)&"/"& STRING(YEAR(),0))

where contains the separated month and year, and 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

