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: Displaying a Number as Years and Months.
Robert has a formula that determines the payback period for certain investments. For instance, with $20,000 investment in energy-savings equipment and an annual energy savings of $3000, the simplistic payback period to recoup the investment is 6.6667 years. Robert wonders how he can make this payback period (6.6667) show as years and months instead of as a decimal number.
This can be done by simply multiplying the portion of the answer at the right of the decimal point by 12, which results in a number of months. Here is one way to get the desired result, assuming that the payback result is in cell A1:
=INT(A1) & " years / " & INT((A1-INT(A1))*12) & " months"
With the value 6.6667 in cell A1, the formula would return "6 years / 8 months".
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (6960) 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: Displaying a Number as Years and Months.
Excel Smarts for Beginners! Featuring the friendly and trusted For Dummies style, this popular guide shows beginners how to get up and running with Excel while also helping more experienced users get comfortable with the newest features. Check out Excel 2013 For Dummies today!
If your company closes out its accounting months at the end of each calendar quarter, figuring out the proper closing dates ...
Discover MoreGiven a particular week number for a year, you may want to figure out the date of the last day in that week. There is no ...
Discover MoreExcel can easily store dates. If you want to increment a date by one month, there are a number of ways you can accomplish the ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2016-10-26 08:20:29
Carlos
Thank you very much :) Spared me some time from having to come with the solution myself.
2016-09-29 09:04:04
efren
Thank you for your tips, as compared to other sites yours are more concise and clear and easy as well.
2015-12-17 04:02:54
Wouter
Excellent tip to display a period of time in an easily readable format. Thanks!
2013-02-23 12:09:36
Juan
Very interesting tip to convert decimal to meaningful format number
Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2017 Sharon Parq Associates, Inc.
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: 
Comments