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 Result as Minutes and Seconds.
Written by Allen Wyatt (last updated August 24, 2019)
This tip applies to Excel 97, 2000, 2002, and 2003
When Deepak divides two numbers, he would like the result shown in minutes and seconds instead of a decimal value. For example, the result of the division 315/130 is 2.42 minutes, but he would like it displayed as 2:25 (2 minutes, 25 seconds).
Welcome to the wonder world of working with time values in Excel. You need to understand that Excel, intrinsically, works with decimal values—such as 2.42. (Actually dividing 315 by 130 results in an answer of 2.42307692.) There are several ways that you can display this as a non-decimal value, such as 2:25. One way is to use a formula such as the following, which returns a text value:
=TEXT(INT(315/130),"0") & ":" & TEXT((315/130-INT(315/130))*60,"0")
The problem with using such a formula, however, is that the resulting text value cannot be used in further calculations. (Well, not without jumping through bothersome and unnecessary hoops.) A better solution is to follow these general steps:
That's it. Your cell now shows 2:25, just as desired. Why does this work? It has to do with how Excel stores dates and times internally. Understand that, in Excel's world, anything to the left of the decimal point (2) is a number of days. Anything to the right of the decimal point (.42307692) is a portion of a full day. Thus, to get value 2.42307692 to something that Excel can understand as a number of minutes and seconds, you need to divide it by the number of minutes in a day (24 * 60, or 1440).
With the value "normalized" to what Excel expects for dates and times, you can then apply formatting to the cell and Excel displays it as you expect.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (6737) 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 Result as Minutes and Seconds.
Best-Selling VBA Tutorial for Beginners Take your Excel knowledge to the next level. With a little background in VBA programming, you can go well beyond basic spreadsheets and functions. Use macros to reduce errors, save time, and integrate with other Microsoft applications. Fully updated for the latest version of Office 365. Check out Microsoft 365 Excel VBA Programming For Dummies today!
Enter a time into a cell and you normally include a colon between the hours and minutes. If you want to skip that pesky ...
Discover MoreNeed to round the time in a cell to a certain value? There are a couple of ways you can do this with a formula.
Discover MoreExcel allows you to easily store dates and times in your worksheets. If you have a range of cells that contain times and ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
There are currently no comments for this tip. (Be the first to leave your comment—just use the simple form above!)
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 © 2025 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