Excel Time: Formatting numbers

Excel Time: YOUR OBJECTIVE

You can enter time values in Excel and calculate with them. This learning module “Excel time” will show you how to enter time values so that Excel recognizes them.

Excel Time: HOW DOES IT WORK?

Time values are stored as fractions of numbers, just as date values are stored as numbers. The number 1 stands for 24 hours and 0.5 stands for 12 hours. For instance, 08:15 a.m. corresponds to 0.34375 and 04:50 p.m. is stored as 0.701388888888889.

For Excel to recognize that you are entering a time value, you have to enter them in an Hours : Minutes pattern. Enter 08:15, for example, and confirm by pressing the [Enter] key.

Excel recognizes the entry as a time value and formats it accordingly:

So that a time value is recognized, you have to apply the Time number format to the cell. As described above, if you enter the number in the 00:00 pattern, Excel formats the cell automatically. However, it’s different if you want to calculate with the time value. In the following example, a calculation is done in cell D3 using the contents of cells B3 and C3. Even though there are time values in cells B3 and C3, the result is not a time value but a decimal number. The reasons for this are: Excel does not calculate with the time values, but uses the corresponding decimal numbers internally instead. The familiar time format is just a way of displaying decimal numbers. Since the target cell D3 is not formatted in the excel  Time format but in the General number format, the result is not displayed as a time value.

To display the result of the calculation as a time value, apply the excel Time number format to the cells.

Click the dialog box launcher in the Number group on the Home tab.

The Format Cells dialog box opens. Click the excel Time category on the Number tab.

You can see the effects of the selected format in the Sample section. If you have selected several cells, the format of the first cell is shown here.

You can choose between several different time formats in the Type: list box.

The “13:30″ format has been selected for our example.

Click the button to confirm the settings. The result is displayed correctly as a time value.

Excel Time: HINT

You can also use the Number Format catalog in the Number group to apply the Time format to a cell or a range of cells.

But you cannot choose exactly how your excel time value is displayed. You are always applying the 13:30:55 (hh:mm:ss) format when you use the catalog.

Excel Time: CAUTION

Among the different format types you find formats that start with a 1 (e.g. 13:30:55) and formats that start with a 3 (e.g. 37:30:55).

These formats have different effects. In our example the excel time values 17:00 and 18:00 are added. A different result is displayed, depending on the format of the target cell:

The result in the upper row has been formatted in the 13:30:55 pattern and shows 11:00:00 as the result. This is the time that results from adding 18 hours to 5 p.m.
The result in the lower row has been formatted in the 37:30:55 pattern and shows the result of adding 17 hours to 18 hours, which is 35 hours.
The formats that start with a 1 show a time value while the formats that start with 3 show a sum of hours.
So if you want to determine a time value, then use a 1-format, and if you want to determine a sum of hours, then use a 3-format.

Did you like it? Then share it or sign up!

You can share this article with your friends or sign up for our newsletter for further information.

Leave a comment

Connect with Facebook

*

Kein Banner zum Anzeigen