Display Times as Hours/Minutes in Excel

How to convert time values into hours/minutes in Excel
iStock/Roberto A Sanchez

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 David Ringstrom, CPA

David Ringstrom

David H. Ringstrom, CPA, is an author and nationally recognized instructor who teaches scores of webinars each year. His Excel courses are based on over 25 years of consulting and teaching experience. His mantra is “Either you work Excel, or it works you.” David offers spreadsheet and database consulting services nationwide.

Replies

Please login or register to join the discussion.

avatar
By Martin
Jun 26th 2015 01:12

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

Thanks (0)
avatar
By Mayur Patel
Jun 26th 2015 01:12

Thank you, you are the life saver.

Thanks (0)
avatar
By Cbhihe
Sep 29th 2015 10:32

Very well, but what happens when you deal with hourly amounts greater than 24 ?

Thanks (1)
avatar
to Cbhihe
Feb 24th 2018 14:39

All you have to do is replace h"hours and "m"minutes" with [hh]"hours and "m"minutes". This includes the excess above 24 hours.

Thanks (0)
avatar
By nadruzz
Apr 18th 2018 07:11

Really helpful

Thanks (0)