ExcelInsider Team
Calculating hours and minutes in Excel is necessary for managing time-based datasets like employee work hours, event schedules, or project durations. Using various formulas and Excel’s built-in tools, users can easily calculate hours and minutes from their datasets.
To calculate hours and minutes in Excel, follow the steps below:
➤ Select the columns containing the start and end times. Then right-click the column headers and go to Format Cells >> Time.
➤ Next, select the cell where you want to display the calculated time in hours and minutes, and put the formula:
=(Start Time)-(End Time)
➤ Replace EndTime and StartTime with their cell references.
➤ Lastly, format the column by right-clicking the column header and heading to Format Cells >> Custom >> [h]: mm.
In this article, we will learn 3 easy methods of calculating time in hours and minutes in Excel.
Calculate Hours and Minutes by Simple Subtraction
This is the simplest and easiest method using which users can quickly calculate time in hours and minutes. We will use a simple formula that subtracts the start time from the end time to calculate the time difference in hours and minutes.
In the sample dataset, we have a worksheet called “Employee Work Duration” containing information about Employee name, Department, City, Start time and End Time. Using a simple subtraction formula, we will calculate the time difference in hours and minutes and display it in column F. The updated dataset will be stored in a separate worksheet called “Subtract Formula”.
Steps:
➤ Open the Subtract Formula worksheet, select and right-click column F header to bring out the context menu, then select Format Cells.
➤ From the Format Cells dialogue box, choose Custom from the category list.
➤ Then, enter [h]:mm in the Type field and click OK to apply the format.
➤ Next, select cell F2 and put the following formula:
=E2-D2
➤ Finally, drag the fill handle from cell F2 down to cell F13 to apply the formula across column F.
➤ The calculated duration in hours and minutes should now be visible in column F.
Use TEXT Function to Calculate Hours and Minutes
This is another effective method that involves the use of TEXT function to display the calculated time difference in hours and minutes. The TEXT function in Excel is used to convert numbers, dates or time values into text, allowing users to format and display data exactly as they want. Working with the same dataset, we will calculate the time difference and display it in hours and minutes format in column F. The updated data will be shown in a separate worksheet named “TEXT Function”.
Steps:
➤Open the TEXT Function worksheet, select cell F2 and put the formula:
=TEXT(E2-D2, “h:mm”)
➤ Next, select cell F2 and drag the fill handle down to F13 to apply the formula across the entire range.
➤ Column F should now display the time duration in hours and minutes format.
Use INT and MOD Functions to Separately Calculate Hours and Minutes
This is a more advanced method that uses Excel’s INT and MOD functions to break down time durations into separate hours and minutes components. Unlike other methods, this method gives users more control, allowing for better customization. The INT function in Excel is used to return the integer part of a number, whereas the MOD function returns the remainder after division. We will again work with the same dataset and, using INT and MOD functions, calculate the time difference in column F. We will display the updated dataset in a separate “Int and MOD Function” worksheet.
Steps:
➤ Open the Int and MOD Function worksheet and in cell F2, put the following formula:
=INT((E2-D2)*24)&”h “&ROUND(MOD((E2-D2)*1440,60),0)&”m”
➧ (E2-D2) subtracts the Start Time from the End Time, finding the duration in days.
➧ “*24” converts the duration from days to hours.
➧ INT() takes the whole number part of the hour and rounds the value.
➧ MOD((E2-D2)*1440,60) converts the duration to minutes and returns the remainder after dividing the total minutes by 60.
➧ ROUND(.., 0) rounds the minute value to a whole number, making sure there are no decimal values.
➧ & "h " & .. & "m" combines the hours and minutes values into a readable format like "3h 45m".
➤ Select cell F2, then drag the fill handle down to cell F13 to apply the formula to the rest of the rows.
➤ You should now see the time duration in hours and minutes being displayed in column F.
Frequently Asked Questions
Why is My Time Calculation Showing Negative Values or Errors?
Your calculated time may show an error or negative values if the End time is earlier than the Start time ( End time < Start Time). To avoid this issue, you can use the following formula:
=IF(E2<D2, E2+1-D2, E2-D2)
This condition assumes that the End time is on the next day and adjusts the calculation accordingly to avoid any negative value.
What is the Best Method for Working With Large Datasets?
If you are working with a large dataset, the simple subtraction method would be the most efficient because of its speed and simplicity. However, if you need more control over the calculation and want to display custom formats, the INT and MOD function method would be the ideal choice.
Concluding Words
Knowing how to calculate hours and minutes in Excel is essential for accurate time tracking in datasets. In this article, we have discussed 3 effective methods of calculating hours and minutes in Excel, including simple subtraction, using the TEXT function and combining INT and MOD functions. Feel free to try all these methods and choose the one best suited for your needs.
