Excel: Covert decimal hours Excel's hours & minutes (8.25 to 8:15)
Posted on: 02/17/2021
I'm frequently asked how to convert decimal hours to Excel's hour and minute format. A couple of examples: 7.75 need to show as 7:45, and 30.25 needs to show as 30:15. The solution is to divide by 24 hours. The formula is =hours/24. When you divide 7.75 by 24, you get 0.322917. After getting the answer, you format the time with an hour and minute format using Format Cells in Excel. The format is h:mm This works since one day (1) is 24 hours. 12 hours divided by 24 is 0.50.
YouTube Video
What about durations over 24 hours?
To display hours over 24 hours, recall I mentioned 30.25? Just adjust the number format. Just wrap the hours, which is the letter h in square brackets [h]:mm
How do I convert to minutes?
To convert to minutes, divide by 1440 instead of 24. 24 hours x 60 minutes = 1,440.
Excel file used in the video
One Excel file
Other Excel articles
-
Working with Time in Microsoft Excel | Time Functions and Conversion
Do you need to add or subtract time in Excel? Some time calculations are easy, but depending on how the time is entered, you may need to use the TIME function or formula to add or subtract time. For example, if you have 9am in B2, and in cell C2, you have 3, for three hours, you can't add them with B2+C2 and get the correct answer without the TIME function.
-
Excel DateDif function - difference between two dates
In Excel if you need to calculate the difference between two dates in years, month, or days, the DATEDIF function will handle it. DATEDIF is "Date & Dif".
-
How to calculate the weighted average in Excel
Need to calculate the weighted average in Excel? It is very easy if you use the sumproduct function in Excel. This is useful for school or work.
Chris Menard
Chris Menard is a Microsoft Certified Trainer (MCT) and is employed full-time as a Trainer for BakerHostetler, one of the nation’s largest law firms. Menard has a YouTube channel with over 900 technology videos covering Excel, Word, Zoom, Teams, Outlook, Gmail, Google Calendar, and other resources that over 16 million viewers have appreciated. Menard also does public speaking at conferences for CPAs and Administrative Professionals. Connect with Chris on LinkedIn at chrismenardtraining.com/linked or on YouTube at chrismenardtraining.com/youtube
Categories
