Loading
Excel.Tips.Net ExcelTips (Menu Interface)

Unique Date Displays

Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Unique Date Displays.

Jon requested help on how to subtract two dates and display the result such that the years were on the left of the decimal and the months on the right. Thus, if you subtracted January 7, 1985 from August 12, 2009, the result would be 24.7.

The easiest way to do this is to simply do your date subtractions as regular, and then use a custom format to display the result. For instance, if the lower date were in cell A2 and the higher date in B2, you could use the following formula in C2:

=B2-A2

You would then follow these steps to format the display of the result in cell C2:

  1. Select the cell. (In this case, cell C2.)
  2. Choose Cells from the Format menu. Excel displays the Format Cells dialog box.
  3. Make sure the Number tab is selected.
  4. In the Category list, at the left side of the dialog box, choose Custom. (See Figure 1.)
  5. Figure 1. The Number tab of the Format Cells dialog box.

  6. In the Type box, at the left of the dialog box, enter the following format: yy.m
  7. Click on OK.

The result is that C2 shows the number of years to the left of the decimal and the number of months to the right. The problem with this is that it will always vary the number of months from 1 to 12, rather than 0 to 11, as one would expect if you were looking for elapsed months.

To overcome this, you could enter the following formula in cell C2:

=(YEAR(B2)-YEAR(A2))+(MONTH(B2)-MONTH(A2))/100

This formula returns the number of years on the left of the decimal and the number of months on the right. The months are always expressed using two decimal places, however. If you wanted to make sure that the months were expressed with no leading zeros, then you would use this formula variation:

=VALUE(ABS(YEAR(B2)-YEAR(A2)) & "." & ABS(MONTH(B2)-MONTH(A2)))

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (2182) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Unique Date Displays.

Related Tips:

Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!

 

Leave your own comment:

*Name:
Email:
  Notify me about new comments ONLY FOR THIS TIP
Notify me about new comments ANYWHERE ON THIS SITE
Hide my email address
*Text:
*What is 5+3 (To prevent automated submissions and spam.)
 
 
           Commenting Terms

Comments for this tip:

Scott Renz    10 Jun 2013, 10:31
I tried this modification to the formula and it seemed to get it right:

=TRUNC((B2-A2)/365.25) & "." & TRUNC(( ((B2-A2)/365.25)-TRUNC((B2-A2)/365.25))*12)
Scott Renz    10 Jun 2013, 10:06
I used my birthday and today's date.
Scott Renz    10 Jun 2013, 10:04
I tried the last formula. It shows the year part as one year more than what I am and the month part 1 or 2 months (depending on how you round) less than what it should be
 
 

Our Company

Sharon Parq Associates, Inc.

About Tips.Net

Contact Us

 

Advertise with Us

Our Privacy Policy

Our Sites

Tips.Net

Beauty and Style

Cars

Cleaning

Cooking

DriveTips (Google Drive)

ExcelTips (Excel 97–2003)

ExcelTips (Excel 2007–2016)

Gardening

Health

Home Improvement

Money and Finances

Organizing

Pests and Bugs

Pets and Animals

WindowsTips (Microsoft Windows)

WordTips (Word 97–2003)

WordTips (Word 2007–2016)

Our Products

Helpful E-books

Newsletter Archives

 

Excel Products

Word Products

Our Authors

Author Index

Write for Tips.Net

Copyright © 2016 Sharon Parq Associates, Inc.