We have a dataset of an employee’s entry time and exit time to the office throughout the week. We need to calculate how much time they worked each weekday.
Method 1 – Using a Subtraction
Steps:
- Click on the E5 cell where you want to calculate your time in hours and minutes.
- Insert the formula below and press the Enter button.
=D5-C5
Note: D5 and C5 both cells should be in 24-hour time format or h: mm custom format.
- You’ll get the employee’s working time for Monday. Place your cursor in the bottom-right corner of your cell.
- The fill handle will appear. Drag the fill handle down to copy the formula through the column.
- Here’s the result.
Read More: Excel Calculate Hours Between Two Times After Midnight
Method 2 – Combine the HOUR and MINUTE Functions
The HOUR function has only one argument which is serial_number. It takes a time value and returns only the hours value of that time.
The MINUTE function has only one argument which is serial_number. It takes a time value and returns only the minute value of that time.
Steps:
- Select E5.
- Insert the following formula and press the Enter button.
=HOUR(D5-C5)&":"&MINUTE(D5-C5)
Formula Breakdown
- =HOUR(D5-C5)
This subtracts the D5 and C5 cells’ time and returns only the hour value of the subtraction.
Result: 8
- =HOUR(D5-C5)&”:”
This concatenates a colon after the previous result.
Result: 8:
- =HOUR(D5-C5)&”:”&MINUTE(D5-C5)
This subtracts the D5 and C5 cells’ time returns the minute value of the subtraction and concatenates it with the precious result.
Result: 8:35
- Go to the bottom-right corner of E5 to get the Fill Handle.
- Drag the Fill Handle down or double-click it to AutoFill through the column.
- Here’s the result.
Method 3 – Use The TIME Function to Calculate Hours and Minutes
The TIME function has three main arguments: hour, minute, and second. The hour argument takes the hour value of a time. Similarly, the minute and second arguments take the minute and second values respectively.
Steps:
- Insert the following formula in cell E5 (the first result cell) and press the Enter button.
=TIME(HOUR(D5),MINUTE(D5),SECOND(D5))-TIME(HOUR(C5),MINUTE(C5),SECOND(C5))
Formula Breakdown
- =TIME(HOUR(D5),MINUTE(D5),SECOND(D5))
This extracts the hour, minute, and second values from the D5 cell’s time and writes in the organized time value.
Result: 17:55
- =TIME(HOUR(C5),MINUTE(C5),SECOND(C5))
This extracts the hour, minute, and second values from the C5 cell’s time and writes in the organized time value.
Result: 9:20
- =TIME(HOUR(D5),MINUTE(D5),SECOND(D5))-TIME(HOUR(C5),MINUTE(C5),SECOND(C5))
This subtracts the second time value from the first time value.
Result: 8:35
- Drag the fill handle down or double-click it to get the other results.
- Here are the results.
Method 4 – Use the TEXT Function
The TEXT function has two main arguments: value and format_text. The value argument takes the value you want to show. The format_text argument takes the format in which you want to show your value.
Steps:
- Insert the following formula in E5 and hit Enter.
=TEXT(D5-C5,"h:mm")
- Drag or double-click the Fill Handle to fill the E column.
- Here are the results.
Method 5 – Use the Excel NOW Function to Calculate Elapsed Time in Hours and Minutes
The NOW function has no arguments. It only returns the current time.
Steps:
- In cell D5, insert the following function, then press the Enter button.
=NOW()-C5
- Drag or double-click the Fill Handle to AutoFill through the column.
- Here’s the result.
Read More: How to Calculate Hours Between Two Times in Excel
Method 6 – Apply the MOD Function to Calculate Time in Hours and Minutes
The MOD function has two main arguments: number and divisor. The MOD function returns the remainder after the division.
Steps:
- Insert the following formula in cell E5 (the result cell) and hit Enter.
=MOD(D5-C5,1)
- Use the Fill Handle to AutoFill through the column for all results.
- Here are the results for the sample.
Method 7 – Combine IF, HOUR, and MINUTE Functions
The IF function has three main arguments: logical test, value_if_true, and value_if_false. The logical test argument is the condition to test. The second argument, value_if_true, contains the value which will be returned if the logical test is true. The third argument, value_if_false, is the value which will be returned if the logical test is false.
Steps:
- Use the following formula in E5 and hit Enter.
=IF(HOUR(D5-C5)>0, HOUR(D5-C5) & " hours, ","") & IF(MINUTE(D5-C5)>0, MINUTE(D5-C5) & " minutes","")
Formula Breakdown
- =IF(HOUR(D5-C5)>0, HOUR(D5-C5) & ” hours, “,””)
This checks if the hour difference of D5 and C5 cells’ time is positive. If positive it returns the hour difference. If negative it would return a blank. Following, it would concatenate the “hours, ” text.
Result: 8 hours,
- =IF(MINUTE(D5-C5)>0, MINUTE(D5-C5) & ” minutes”,””)
This checks if the minute difference of D5 and C5 cells’ time is positive. If positive it returns the minute difference. If negative it would return a blank. Following, it would concatenate the “minutes, ” text.
Result: 35 minutes
- =IF(HOUR(D5-C5)>0, HOUR(D5-C5) & ” hours, “,””) & IF(MINUTE(D5-C5)>0, MINUTE(D5-C5) & ” minutes”,””)
The formula concatenates the two previous results via the & operator.
Result: 8 hours, 35 minutes
- Use the Fill Handle to fill in through the column.
Here are the results.
Things to Remember
- Formatting is very important when calculating times in Excel. It is better to set the format in 24-hour format from the format window. You can choose a custom format as h::mm too.
- Sometimes, a result shows a #### error value. This happens for two reasons, such as Narrow Columns and Negative Results. For narrow column cases, expand the column. For the negative results case, use the IF function to apply different statements or formulas when negative results appear.
Download the Practice Workbook
Related Articles
- How to Calculate Hours from Date and Time in Excel
- How to Calculate Time Difference Between AM and PM in Excel
- How to Calculate Difference Between Two Dates and Times in Excel
- Calculate Hours Between Two Dates and Times in Excel Excluding Weekends
- How to Calculate Travel Time Between Two Cities in Excel
<< Go Back to Calculate Hours | Calculate Time | Date-Time in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
I don’t need the start and end times, I am just looking to add time values. On Monday, I worked 8h 28m min driving and 4h 59m not driving, on Tuesday, 9h 11m driving and 3h 2m not driving, ect… how do i set up a spread sheet where put those hours in and it auto sums? Thanks
Hi, Bill!
Thank you for your query.
Regarding your query, just make a dataset table and use the SUM function or the addition functionality of Excel. In our article, we have used the subtraction functionality; in your requirements, you just need to utilize the addition functionality of Excel.
Regards,
Tanjim Reza