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.

**Read More: **Calculate Elapsed Time Between Two Dates in Excel

### 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**

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