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.

## Replies (6)

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

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

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.

Really helpful

Thank you. Suppose I have in cells : 1.30 - 0.30 how can I turn their sum into : 2:00 ??