How to Use Display Time As a Fraction of an Hour in Excel

Using a simple formula, you can make Excel display time in fractions.

When you enter a time in Microsoft Excel, it is displayed in the familiar time format, but its internal value is stored as a fraction of the day. As an example, 12:00 noon is stored as half of a day. If you were to reformat the cell as a fraction, you would indeed see the time value transformed into "1/2." To convert this fraction into a fraction of an hour, you need to multiply this value by the number of hours in a day.

Instructions

1. Open your spreadsheet in Microsoft Excel.

2. Right-click the cell containing the time value and select "Format Cells."

3. Click the "Number" tab in the "Format Cells" window.

4. Click "Fraction" in the "Category" list and select the format in the "Type" list. You can select single, double and triple-digit denominators, or you can select to display more specific denominations such as halves or quarters.

5. Click "OK." The time value is now displayed as a fraction of the day. As an example, "4:30 AM" is reformatted to "1/5" when configured with a single-digit denominator.

6. Double-click the cell to enter editing mode, which displays the fraction as a decimal. In the example, "1/5" is displayed as "0.1875" in editing mode.

7. Type "=" before the displayed number and "*24" after the number. Press "Enter" to display the time as a fraction of an hour. In the example, changing the cell value to "=0.1875*24" makes the cell display "4 1/2."

References

• PC User: How Do You Convert Time to Decimal Values in Excel?