LikeOffice.com

excel utility

Keep In Touch:
 contact us  facebook
 
Get HELP with your Excel Project:
 Excel Formula
 List of Excel Formula examples
 
Home >> excel 2007 >> Excel 2007 - Date and time formula

This page presents how to work with Microsoft Excel 2007 features and its redesigned interface. You may also visit our Excel Question page.

 

 

Add or subtract dates

Excel 2007

Let's say that you want to adjust a revised schedule date by adding two weeks to see what the new completion date will be, or you may want to determine how long an activity will take to complete in a list of project tasks. You can add or subtract a number of days to or from a date in a simple formula, or you can use functions that are designed to work specifically with dates in Excel.

In this article

Add days to or subtract days from a date

Add months to or subtract months from a date

Add years to or subtract years from a date

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

 Top of Page

Add days to or subtract days from a date

Suppose that an account balance of yours is due on February 8, 2008. You want to transfer funds to your checking account so that those funds arrive 15 calendar days before that 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 2008 bill so that those funds are available 15 days before that date.

  1. In cell A1, type 2/8/08.
  2. In cell B1, type =A1-15.
  3. In cell C1, type =A1+30.
  4. In cell D1, type =C1-15.

Cells A1 and C1 show the due dates (2/08/08 and 3/09/08) for the February and March account balances, and cells B1 and D1 show the dates (1/24/08 and 2/23/08) by which you should transfer your funds for those due dates.

 Top of Page

Add months to or subtract months from a date

You can use the EDATE function to quickly add or subtract a specific number of whole months to or from a date.

The EDATE function requires two values (also referred to as arguments): 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/08",-5). This formula results in the date 9/15/07.

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/08."

For example, you want to add 16 months to October 16, 2007.

  1. In cell A5, type 10/16/07.
  2. In cell B5, type =EDATE(A5,16).

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

  1. In cell C5, type =EDATE("10/16/07",16).

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

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

 Note    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/09 might be displayed as 39860.

Excel stores dates as sequential serial numbers so that they can be used in calculations. By default, January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,447 days after January 1, 1900. Microsoft Excel for the Macintosh uses a different date system as its default.

  1. If your results appear as serial numbers, select cells B5 and C5 and continue with the following steps.
  2. On the Home tab, in the Cells group, click Format, and then click Format Cells.
  3. In the Format Cells dialog box, click the Number tab.
  4. Under Category, click Date, and then click OK. The value in each of the cells should appear as a date instead of a serial number.

 Top of Page

Add years to or subtract years from a date

The example may be easier to understand if you copy it to a blank worksheet.

How do I copy an example?

  1. Select the example in this article.

Important  Do not select the row or column headers.

Selecting an example from Help

  1. Press CTRL+C.
  2. In Excel, create a blank workbook or worksheet.
  3. In the worksheet, select cell A1, and press CTRL+V.

Important  For the example to work properly, you must paste it into cell A1 of the worksheet.

  1. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

 

1

2

3

4

5


6


7


8

A

B

Date

Years to add (or subtract)

6/9/2007

3

9/2/2007

-5

12/10/2008

25

Formula

Description (Result)

=DATE(YEAR(A2)+B2,MONTH(A2),DAY(A2))

Adds 3 years to 6/9/2007 (6/9/2010)

=DATE(YEAR(A3)+B3,MONTH(A3),DAY(A3))

Subtracts 5 years from 9/2/2007 (9/2/2002)

=DATE(YEAR(A4)+B4,MONTH(A4),DAY(A4))

Adds 25 years to 12/10/2008 (12/10/2033)

How the formula works

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/2007), and returns 2007 as the year. The formula then adds 3 (the value in cell B2) to the year value, which results in 2010. 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/2010.

You can use a similar formula to add months to a date. For example, by using the previous sample data, you can add 9 months to the date 6/9/2007 by using the following formula: =DATE(YEAR(A2),MONTH(A2)+9,DAY(A2)). This formula returns the date 3/9/2008.

 Top of Page

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

The example may be easier to understand if you copy it to a blank worksheet.

How do I copy an example?

  1. Select the example in this article.

Important  Do not select the row or column headers.

Selecting an example from Help

  1. Press CTRL+C.
  2. In Excel, create a blank workbook or worksheet.
  3. In the worksheet, select cell A1, and press CTRL+V.

Important  For the example to work properly, you must paste it into cell A1 of the worksheet.

  1. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

 

1

2

3


4


5

A

B

Date

 

6/9/2007

 

Formula

Description (Result)

=DATE(YEAR(A2)+3,MONTH(A2)+1,DAY(A2)+5)

Add 3 years, 1 month, and 5 days to 6/9/2007 (7/14/2010)

=DATE(YEAR(A2)+1,MONTH(A2)+7,DAY(A2)+5)

Add 1 year, 7 months, and 5 days to 6/9/2007 (1/14/2009)

The formulas in the example have the following arguments (an argument is a value that is supplied to a function).

Formula to add dates

start_date: A date or reference to a cell that contains a date.

add_year: The number of years to be added.

add_month: The number of months to be added.

add_day: The number of days to be added.

How the formula works

In each of the formulas, 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 first formula), the YEAR function is used on the date in cell A2 (6/9/2007), and returns 2007 as the year. The formula then adds 1 to the year value, which results in 2008. 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 2009. 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 (2009, 1, and 14) into a date that is one year, seven months, and 5 days in the future  1/14/2009.

 Top of Page

 

                  

Microsoft® and Microsoft Excel® are registered trademarks of Microsoft Corporation.
©2003-2013 LikeOffice - Privacy Policy - Contact us