By

Long before there were apps, there was Excel. It does almost everything, from calculating mortgage payments to finding out precisely how many days old your Great Aunt Mary is. Below are some of our top date calculation tips.

Tips were compiled and written by Vitalyst staff.

Long before there were apps, there was Excel.

Counting Days Between Two Dates

Microsoft Excel 2003, 2007, 2010

Do you need to know how many days are between two dates? If you ever have to figure out how long before a payment is due, how long it takes to manufacture a product, how many days shipping took, or how long before a perishable product is out of date, then this tip will help you.

For this example we will create a blank worksheet and enter the following data:

  • In cell A1: 9/1/2010
  • In cell A2: 10/3/2010

Excel 2003 and older:

  1. To determine the difference between two dates simply subtract the earlier date from the later date. Using our example worksheet the formula would be =A2-A1. The result of the formula will display as a date; in order to display the number of days between the dates we need to change the cell format.
  2. To view the result as a number, select the cell, then go to the Format menu and click Cells.
  3. Click the Number tab in the Format Cells dialog box.
  4. Choose Number in the Category list box, change the Decimal places to zero (0), and then click OK.
  5. To determine the number of days between the current date and a past date we can use the Today function to return the current date. Using our example data: if cell A2 contains the date our library books were due we would calculate the number of days overdue using the formula =TODAY()-A2.
  6. Repeat steps two (2) through four (4) to format the result as a number.

Excel 2007 and 2010:

  1. To determine the difference between two dates simply subtract the earlier date from the later date. Using our example worksheet the formula would be =A2-A1. The result of the formula will display as a date; in order to display the number of days between the dates we need to change the cell format.
  2. To view the result as a number, select the cell and then go to the Home tab and click the Format drop-down in the Cells group, and choose Format Cells.
  3. Click the Number tab in the Format Cells dialog box.
  4. Choose Number in the Category list box, change the Decimal places to zero (0), and then click OK.
  5. To determine the number of days between the current date and a past date we can use the Today function to return the current date. Using our example data: if cell A2 contains the date our library books were due we would calculate the number of days overdue using the formula =TODAY()-A2.
  6. Repeat steps two (2) through four (4) to format the result as a number.

How to Extract the Month from a Date

Microsoft Excel 2003, 2007, 2010

For this tip, enter a date in cell A1, for example 7/4/2010. In cell B1 you would like to return the month of that date, which would be 7. In order to return the correct month we are going to use the MONTH function.

In cell B1, type the formula =MONTH(A1) which would return 7 since that is the month of the date in A1.

The MONTH function takes a date or serial number as its argument:

  • In the above example the date entered into a cell was 7/4/2010 which returns a 7.
  • A date could also be entered as a formula such as =DATE(2011,1,15). Using MONTH to return the month from this value would result in 1.
  • A date could also be entered as a serial number. 39000 formatted as a date would be 10/10/2006, and the formula would return a 10.

NOTE: To see an abbreviated or full name of the month you can make use of a custom format. Say you have the date 7/4/2010 in cell A1. In B1 you can write the formula =A1, which should return the same date. Now you can right-click on B1 and choose Format Cells. Click on the Number tab and choose the Custom category of the left hand side. If you want the see the abbreviated way to spell the month, type in mmm under the Type section. If you would like to see the full name, type mmmm.

How to Calculate the Last Day of the Month

Microsoft Excel 2003, 2007, 2010

From time to time it may be necessary to return the last day of a month in the future when calculating loan maturity dates or other due dates. Of course, knowing that September is two months from July is nice and those of us who paid attention in school know that September has 30 days, but the EOMONTH function in Excel does the work for us and is easily updatable.

To see how this function works, type a date in cell A1. In cell B1, enter the following formula: =EOMONTH(A1,2). The first argument in the formula, the A1 refers to the start date, or our starting point. The second argument indicates how far into the future to look before returning the last day of the month. For example, if the date in A1 is July 2, 2011, then the formula will return September 30, 2011 — the end of the second month from the start date. Replacing the 2 in our formula with a 3 would return October 31, 2011.

The formula will return the date as a serial number, so you may want to change the cell display formatting to either Short or Long Date, depending on preference.

NOTE: In Excel 2003, you may receive a #NAME? error upon entering the formula. If this happens, you need to enable the Analysis Toolpack add-in. To do this, go to Tools, Add-Ins and check the box for Analysis Toolpack and click OK. Once the add-in is enabled, Excel can make sense of the function.

The EOMONTH function can also be used to return the first day in the current month by using this formula: =EOMONTH(TODAY(),-1)+1. This formula will look at the current date (the TODAY function) and return the first of the month. It does this by finding the last day of the preceding month (that is the reason for the -1) and adding one day to it. Another way to return the first day of any month is with this formula: =A1-(DAY(A1)-1). This formula uses the DAY function, which returns the day of the month as an integer between 1 and 31. Subtracting this from the date in A1 gives the date of the last day of the preceding month; adding 1 day to that number gives the first day of the month containing the A1 date

How to Use the DATEDIF Function

Microsoft Excel 2003, 2007, 2010

Do you need to determine the number of years between two dates? For instance, it might be important to know how many years an employee has worked for you or to determine what age an employee is for benefits eligibility. The DATEDIF function combined with the TODAY function can provide this information and more for you.

In this tip we will specifically deal with years. The DATEDIF function can also give you results in months, days, and several other variations.

  1. Enter the initial date, 1/1/1987 in cell A1.
  2. Click in B1 and type the following formula: =DATEDIF(A1,TODAY(),”y”)
  3. Press ENTER. You should get the result of 24.

See the extra tip below for a more informative result if you will not be doing any further calculations with the number.

The DATEDIF function has three parts, separated by commas. The first part of the formula contains the initial or start date. In this case, we substituted the cell reference (A1) containing that date instead. The second part of the formula usually contains the ending date. In our case, we substituted the TODAY() function which represents the current date since it is a function that updates as the function is calculated. The third part of the function determines the unit of measure for the result, in our example years (“y”). The function can also report months or days.

Extra Tip

If you would like the see the result as “21 years,” enter the formula with this variation:

=DATEDIF(B4,TODAY(),”y”)&” years”

We used the ampersand (&) symbol to concatenate the result with the text “years” to provide a more informative solution. Make sure you include a space inside the quotes before the word years. Adding text to the number has one side effect; you cannot use that number in further mathematical calculations easily.

To display the result of the DATEDIF function in months or days, simply substitute the appropriate letter “m” or “d” for the letter “y” in the formula.

For more tips on Excel and other applications, visit our eTips Library.

Leave a Comment

Your email address will not be published. Required fields are marked *