Add or subtract dates in Excel for Mac

Add days to or subtract days from a date

Suppose that an account balance is due on February 8, 2012. You want to transfer funds to your checking account so that those funds arrive 15 calendar days before the due date. In addition, you know that your account has a 30-day billing cycle, and you want to determine when you should transfer funds for your March 2012 bill so that those funds are available 15 days before that date. To do this, follow these steps:

1. Open a new sheet in a workbook.

2. In cell A1, type 2/8/12.

3. In cell B1, type =A1-15, and then press RETURN .

   This formula subtracts 15 days from the date in cell A1.

4. In cell C1, type =A1+30, and then press RETURN .

   This formula adds 30 days to the date in cell A1.

5. In cell D1, type =C1-15, and then press RETURN .

   This formula subtracts 15 days from the date in cell C1.

   Cells A1 and C1 show the due dates (2/8/12 and 3/9/12) for the February and March account balances. Cells B1 and D1 show the dates (1/24/12 and 2/23/12) by which you should transfer your funds so that those funds arrive 15 calendar days before the due dates.

Add months to or subtract months from a date

Suppose that you want to add or subtract a specific number of whole months to or from a date. You can use the EDATE function to quickly do this.

The EDATE function requires two values (also referred to as argument): the start date and the number of months that you want to add or subtract. To subtract months, enter a negative number as the second argument (for example, =EDATE("2/15/12",-5)). This formula subtracts 5 months from 2/15/12 and results in the date 9/15/11.

You can specify the value of the start date either by referring to a cell that contains a date value or by entering a date enclosed in quotation marks, such as "2/15/12".

For example, suppose you want to add 16 months to October 16, 2012.

1. In cell A5, type 10/16/12.

2. In cell B5, type =EDATE(A5,16), and then press RETURN .

   The function uses the value in cell A5 as the starting date.

3. In cell C5, type =EDATE("10/16/12",16), and then press RETURN .

   In this case, the function uses a date value that you enter directly, "10/16/12."

   Cells B5 and C5 both show the date 2/16/14.

   Why do my results appear as numbers instead of dates?

   Depending on the format of the cells that contain the formulas that you entered, Excel might display the results as serial numbers; in this case, 2/16/14 might be displayed as 41686. If your results appear as serial numbers, follow these steps to change the format:

   1. Select cells B5 and C5.

   2. On the Home tab, under Format, select Format Cells, and then select Date. The value in each of the cells should appear as a date instead of a serial number.

Add years to or subtract years from a date

Suppose that you want to add or subtract a certain number of years from certain dates, as described in the following table:

1. In a new sheet, type 6/9/2009 in cell A2, and then type 3 in cell B2.

2. In cell A3, type 9/2/2009, and then type -5 in cell B3.

3. In cell A4, type 12/10/2010, and then type 25 in cell B4.

4. In cell A6, type =DATE(YEAR(A2)+B2,MONTH(A2),DAY(A2)), and then press RETURN .

   This formula adds the value in cell B2 (3 years) to the value in cell A2, for a result of 6/9/2012.

5. In cell A7, type =DATE(YEAR(A3)+B3,MONTH(A3),DAY(A3)), and then press RETURN .

   This formula adds the value in cell B3 (–5 years) to the value in cell A3, for a result of 9/2/2004.

6. In cell A8, type =DATE(YEAR(A4)+B4,MONTH(A4),DAY(A4)), and then press RETURN .

   This formula adds the value in cell B4 (25 years) to the value in cell A4, for a result of 12/10/35.

   In each of the three formulas, a specified number of years from column B is added to the year value that is derived from the date in column A.

   For example, in cell A6, the YEAR function is used on the date in cell A2 (6/9/2009), and it returns 2009 as the year. The formula then adds 3 (the value in cell B2) to the year value, which results in 2012. In the same formula, the MONTH function returns the value 6, and the DAY function returns the value 9. The DATE function then combines these three values into a date that is three years in the future: 6/9/2012.

Add a combination of days, months, and years to a date

Suppose that you want to add a combination of days, months, and years to a specific date.

1. In a new sheet, type 6/9/2012 in cell A2.

2. In cell A4, type =DATE(YEAR(A2)+3,MONTH(A2)+1,DAY(A2)+5), and then press RETURN .

   This formula adds 3 years, 1 month, and 5 days to 6/9/2012, for a result of 7/14/2015.

3. In cell A5, type =DATE(YEAR(A2)+1,MONTH(A2)+7,DAY(A2)+5), and then press RETURN .

   This formula adds 1 year, 7 months, and 5 days to 6/9/2012, for a result of 1/14/2014.

   In each formula, a specified number of years, months, and days are added to the date that is contained in cell A2.

   For example, in cell A5, the YEAR function is used on the date in cell A2 (6/9/2012), and it returns 2012 as the year. The formula then adds 1 to the year value, which results in 2013. The MONTH function returns the value 6, and 7 months are added to that value. Because the total of 6 months plus 7 months is 13 months, the DATE function adds 1 year to the year value, resulting in 2014. The DATE function then subtracts 12 from the month value, resulting in a value of 1 for the month. The DAY function returns the value 9, and 5 days are added to that, resulting in 14. Finally, the DATE function combines these three values (2014, 1, and 14) into a date that is one year, seven months, and 5 days in the future: 1/14/2014.