Change Minutes from a Decimal to a Percentage of an Hour

Add to Favorites
Author:

Change minutes stored as a decimal into a fraction of an hour. For instance, if time is stored as 8.15, 8 hours and 15 minutes, this is not very helpful when performing calculations such as a costing analysis; it is better to have the time converted to 8.25, or 8 hours and 25% of one hour.  With the new number, you can multiple it by, for instance, an hourly rate to get how much to charge for something.

This tutorial assumes that a person or program stored time in the format stated above. Excel does not store time this way by default.

Formula

A simple formula like this will convert the time:

=TRUNC(A1)+(A1-TRUNC(A1))*1.66666667

This assumes that the time is in cell A1.

This formula is not too complex, it chops off the integer portion of the number using the TRUNC() function and then multiplies the decimal amount by 1.66666667 in order to convert the minute amount to a percentage of an hour amount; then, the formula puts the new number back with the integer.

Here it is in Excel:

That returns:

15 minutes is 25% of one hour and that is why 8.15 becomes 8.25.

Notes

This tutorial has nothing to do with how Excel stores times. This applies to a situation where a time was entered in the above format by a person or another program.

Download the attached file to work with this example in Excel.


Excel Function: TRUNC()
Downloadable Files: Excel File

Question? Ask it in our Excel Forum


Our Excel Courses

  • Skill Level: All Levels
  • |
  • 5 hours+

How to make a fully featured professional form in Excel that is unbreakable. This includes how to use the form to store, view, edit, and delete data from a data storage worksheet.

  • Skill Level: All Levels
  • |
  • 2 hours

Send Emails from Excel using VBA and Macros. This course starts from the Basics and builds up to more advanced examples with attaching workbooks, worksheets, PDF's, automatically sending emails, including a signature, error handling, increasing speed, and more.

Similar Content on TeachExcel
Input Form to Get Data and Store it in Another Tab in Excel
Tutorial: How to make a user input form in Excel and have the data stored on another worksheet at th...
Ignore Blanks in a Data Validation List in Excel
Tutorial: I will show you 3 ways to remove the blanks from a Data Validation dropdown menu in Excel...
Run a Macro when a User Does Something in the Workbook in Excel
Tutorial: How to run a macro when a user does something within the Workbook in Excel, such as openi...
Variables in Macros VBA for Excel - Practical Guide
Tutorial: This is a practical guide to using Variables in Macros and VBA for Excel. I will tell you ...
Add Values to a ListBox
Tutorial: How to fill a Listbox with values in a UserForm. By default, a Listbox in a form will be e...
Getting Data from a UserForm
Tutorial: How to get data from a UserForm in Excel, including from: text inputs (TextBox), list boxe...