Not only do spreadsheets excel at calculating numbers, they can also calculate dates too. Imagine that you have taken out a 6-month loan and you want to know when it will be paid off (assuming that it is 6 calendar months from the loan start date) or maybe you have been assigned a project which must be completed on a certain date.

You know that it will take you exactly 6 months to complete the project so you need to know when to start.

The EDATE function, which can be used in most spreadsheet applications including Excel, Numbers and Google Spreadsheets, as well as open source applications such as Libre Office and Open Office will calculate the date that is a defined number of months away from another date.

The structure of the function is =EDATE(date,months)

Example 1: A Loan

Example_1_A_Loan.png

Imagine that you have taken out a 6 month loan starting on 4th May 2012. The formula in B5 will calculate when the loan will be paid off (assuming that it is 6 calendar months from the loan start date).

The formula in B5 is =EDATE(B3,B4)

Example 2: A Project

Example_2_A_Project.png

You have been assigned a project which must be completed on 8th October 2012. You know that it will take you exactly 6 months to complete the project so you need to know when to start the project.

The formula in B5 is =EDATE(B3,-B4).

The second parameter (or argument as it’s known) is a negative and therefore subtracts 6 months from the date in B3.

Excel: Analysis TookPak Add-In

Excel_Analysis_TookPak_Add-In.png

Note that if you are using Excel 2003 or earlier you have to install the Analysis ToolPak add-in otherwise the result of the EDATE function will display as #NAME.

To install the add-in, select Tools > AddIns and tick Analysis ToolPak in the Add-ins dialog box