Written by Allen Wyatt (last updated December 8, 2018)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365
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, 1991 from August 12, 2019, the result would be 28.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:
Figure 1. The Number tab of the Format Cells dialog box.
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. (Hence, the result of 28.8 in C2.)
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 result is 28.07 in C2.)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)))
The result in C2 is now 28.7.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (11057) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365. You can find a version of this tip for the older menu interface of Excel here: Unique Date Displays.
Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!
Do you need to figure out the date for the first Tuesday of any given month? Excel is incredibly flexible when it comes ...
Discover MoreWhen working with dates and the relationship between dates, Excel provides a variety of worksheet functions that may ...
Discover MoreWork in an industry that uses ISO standards when it comes to working with dates? You'll love the formula in this tip ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2019-05-08 14:42:30
Dennis Costello
Another problem with =VALUE(ABS(YEAR(B2)-YEAR(A2)) & "." & ABS(MONTH(B2)-MONTH(A2))) - it gives the wrong answer when B2 is earlier in the year than A2. For instance, if A2 contains 9/13/2018 and B2 5/8/2019, there are 8 months and no full years between those two dates. The simple "B2-A2" approach shows this properly as "00.8", but the formula gives 1.4.
=VALUE(YEAR(B2) - YEAR(A2) - (MONTH(A2) > MONTH(B2)) & "." & MONTH(B2) - MONTH(A2) + (MONTH(A2) > MONTH(B2)) * 12)
This only gives sensible answers when B2 is later than A2, but that is consistent with the original problem statement. Clearly one could build a formula around =IF(MONTH(A2) > MONTH(B2), ..., ...) but I like to use the fact that TRUE becomes 1 and FALSE becomes 0.
Interestingly, the B2-A2 approach yields 00.2 when the dates are for instance 1-Apr and 31-May of the same year (i.e., it rounds the interval to the closest number of months) but the other formulas yield 0.1 (because May is only 1 month later than April).
2018-12-11 20:01:17
Peter Atherton
The DAteDif function has been done somwhere else on this site. But here is a solution using it.
=DATEDIF(A1,B1,"y")+(DATEDIF(A1,B1,"ym")*0.01)
2018-12-10 18:37:07
Yvan Loranger
Problem with =VALUE(ABS(YEAR(B2)-YEAR(A2)) & "." & ABS(MONTH(B2)-MONTH(A2))) is that diffs of 1 & 10 months would both appear as .1 (:
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2023 Sharon Parq Associates, Inc.
Comments