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. Display the Format Cells dialog box. (In Excel 2007 display the Home tab of the ribbon and click the small icon at the lower-right corner of the Number group. In older versions of Excel choose Cells from the Format menu.)
3. Make sure the Number tab is selected. (Click here to see a related figure.)
4. In the Category list, at the left side of the dialog box, choose Custom.
5. In the Type box, at the left of the dialog box, enter the following format: yy.m
6. 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)))