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.
Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!
Need to know if a cell contains a time value? Excel doesn't contain an intrinsic worksheet function to answer the ...
Discover MoreWhen using Excel to calculate elapsed time, there can be all sorts of criteria that affect the formulas you would ...
Discover MoreIf you have a bunch of times entered into cells without the colon between the hours and minutes, chances are good that ...
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 © 2024 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