Display Times as Hours/Minutes in Excel

istock_roberto_a_sanchez_clock.jpg

How to convert time values into hours/minutes in Excel
iStock/Roberto A Sanchez
Columnist
Share this content
2

Working with time values in Excel often gives users fits. This is because Excel stores times as a decimal portion of a day. Thus 12 hours is the same as 0.5. Three and a half-hours is represented as 0.145833333333333. Understanding this decimal concept along with Excel’s time-based number formats will help you whip time-based values into shape.

Let’s first say that you have the value 181 in a worksheet cell, and you wish to show this as 3:01 for 3 hours and one minute. To do so, we’ll need to divide 181 by 1440 to convert 181 minutes to its decimal equivalent: 1440 is derived from 24 hours in a day, and 60 minutes per hour, thus 24*60=1440. As shown in Figure 1, 181 divided by 1440 is 0.12569.

Figure 1: Convert minutes into their decimal format by dividing by 1440 and then applying a custom number format.

We can now transform this seemingly incomprehensible number into a useful format by way of Excel’s Format Cells dialog box, also shown in Figure 1:

  • Select one or more cells that contain a decimal-based time value.
  • Launch the Format Cells dialog box. A keyboard shortcut for doing so is Ctrl-1.
  • On the Number tab choose Custom. Scroll down the list of custom formats and choose h:mm, and then click OK. A shortcut for closing the Format Cells dialog box is to double-click on h:mm.

Alternatively, let’s say that you’d like the cell to actually read 3 hours and 1 minute, or 3 hour(s) and 1 minute(s). As shown in Figure 2:

  • Select one or more cells that contain a decimal-based time value.
  • Launch the Format Cells dialog box.
  • On the Number tab choose Custom.
  • Enter this format code in the Type field:

h" hours and "m" minutes"

  • Click OK to close the Format Cells dialog box.

You can also use h" hour(s) and "m" minute(s)" if you want to include the potential for plural hours and minutes.

Figure 2: A custom number format can spell out the words hours and minutes yet keep the underlying cell value numeric.

The format code has two components: numeric placeholders and text. In this case the letter h signifies the hour portion of our time, while the words hours and with a space before and after it is enclosed in double-quotes. The m for minutes appears immediately after the second double-quote, and then minutes with a space before it appears in double-quotes.

The benefit to custom number formats is that words appear in a worksheet cell, but to Excel this is still a numeric value that we can perform math with.

Figure 3: The CONVERT has a variety of uses.

There’s always any number of ways to perform calculations in Excel, but I will mention one other approach, which is Excel’s CONVERT function. As shown in Figure 3, the CONVERT function has three arguments:

  • Number – This can be any number that represents a variety of measurements. For this article I’ll focus solely on time, but CONVERT allows you to convert a dozens of measurements, such as pounds to kilos, stone to ton, even mile to nautical mile. These barely scratch the surface of what CONVERT can convert.
  • From_Unit – This is a code that represents the value you’re converting from. In this case we’ll use “mn” to signify minutes
  • To_Unit – This is a code that represents the value you’re converting to. In this case we’ll use “hr” to signify hours.

When you choose a From_Unit, Excel automatically limits the available To_Unit arguments to valid choices, so that you won’t inadvertently try to convert minutes to tons or nautical mile. The resulting amount includes a decimal value for minutes, but this is a fraction of an hour, as opposed to a fraction of a day as was shown in Figure 1.

About the author:

David H. Ringstrom, CPA, heads up Accounting Advisors, Inc., an Atlanta-based software and database consulting firm providing training and consulting services nationwide. Contact David at david@acctadv.com or follow him on Twitter. David speaks at conferences about Microsoft Excel, teaches webcasts for CPE Link, and writes freelance articles on Excel for AccountingWEB, Going Concern, et.al.

Replies

Please login or register to join the discussion.

Excel-lent, thank you - sorry just had to throw that in ;-)

Thank you, you are the life saver.