Save content
Have you found this content useful? Use the button above to save it to your profile.
CFO
iStock_Baona_Spreadsheets

Excel tip: Convert hours to minutes

by
11th Jun 2015
Save content
Have you found this content useful? Use the button above to save it to your profile.

David H Ringstrom takes you through a series of simple sequences that will help you master Excel’s mysterious time-based value system.

The mysteries of Excel’s technique for handling time values has perplexed users for many years.

The problem stems from the way Excel stores times as a decimal portion of a day. As a result 12 hours is represented as 0.5. Three and a half-hours becomes 0.145833333333333. Once you’ve got a grip on this decimal concept, you should be able to adjust to the quirks of Excel’s time-based number formats.

For example, if you have the value of 181 minutes in a worksheet cell that you want to display as 3:01 to represent 3 hours and one minute, you would 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, 24*60=1440. The result of the calculation is shown below. Turning this incomprehensible number into a useful format is achieved using Excel’s Format Cells dialog box.

  • 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.

Excel screencap

If you would prefer the cell to read 3 hours and 1 minute, or 3 hour(s) and 1 minute(s), this is the sequence to follow:

  • Select one or more cells that contain a decimal-based time value.
  • Launch the Format Cells dialogue 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 dialogue box.

You can choose the h" hour(s) and "m" minute(s)" options if you want to allow for plural hours and minutes measurements.

Excel screen capture

The format code is based on two components: numeric placeholders and text. The letter h signifies the hour, while “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.

These custom number formats allow the words hours and minutes to appear in a worksheet cell, but Excel continues to treat it as a numeric value for calculation purposes.

Excel screen capture

As is customary with Excel, there are usually several ways to get where you want. An alternative to the custom number formats described above is made possible by the CONVERT function, which has three arguments:

  • Number – This tip focuses on time calculations, but CONVERT lets you convert measurements such as pounds into kilos, or stones to tonnes and miles to nautical miles. It’s well worth exploring CONVERT more deeply if you ever need to wrestle with such challenges
  •  From_Unit – This represents the value you’re converting from. In this example “mn” signifies minutes
  • To_Unit – This portion refers to the value you’re converting to - this case “hr” to signify hours.

When you choose a From_Unit, Excel automatically limits the available To_Unit arguments to valid choices to prevent you converting minutes to tonnes or nautical miles. 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 show in the first custom number format in the first picture in this article.

Tags:

Replies (0)

Please login or register to join the discussion.

There are currently no replies, be the first to post a reply.