Microsoft KB Archive/24222

Excel: Adding or Subtracting Months from a Date PSS ID Number: Q24222 Article last modified on 04-23-1991 PSS database name: M_eXceL

1.00 1.03 1.04 1.06 1.50

MACINTOSH

Summary:

When Microsoft Excel versions 1.50 and earlier add or subtract months from a date, an incorrect answer may appear. For example, subtracting three months from the date 01/03/87 (serial date 30318) yields an answer of 2/03/87. The following formula results in the date 2/03/87:

=DATE(YEAR(30318),MONTH(30318)-3,DAY(30318))

To avoid this problem, use the following formula when adding or subtracting months from dates

=DATE((YEAR(date1)+INT((MONTH(date1)+date2)/12)),IF(MOD(MOD (MONTH(date1),12)+date2,12)<>0,MOD(MONTH(date1)+date2,12),12),DAY(date1))

where:

date1 = the original serial date date2 = the number of months you are adding or subtracting

This formula will display the serial number of the appropriate date until the cell is formatted with a date format.

More Information:

Microsoft has confirmed this to be a problem in Microsoft Excel versions 1.00, 1.03, 1.04, 1.06, and 1.50. This problem was corrected in Microsoft Excel version 2.20.

Copyright Microsoft Corporation 1991.