## Formula 1 – Excel Subtraction Formula To Calculate Time Worked

The formula to calculate the time difference is to subtract the start time from the end time.

**Time Worked = End Time – Start Time**

The sample dataset below shows some time differences.

The formula we used:

`=C5-B5`

The problem is a difference in the time format. For the first day, we got 8:00 AM instead of 8 hours.

You need to change it to Custom format to get the result in hours, minutes, and seconds format.

- Select the range of cells.

- Press
**Ctrl+1**on your keyboard.

- From the
**Format Cells**dialog box, click**Number**, select**Custom**from**Category**. From the**Type**, select**h:mm: ss**format. Click on**OK**.

It will display the time worked in hours, minutes, and seconds format.

**Read More: **How to Calculate Hours Worked Minus Lunch with Excel Formula

## Formula 2 – Excel Formula To Calculate Time Worked in Hours, Minutes, or Seconds

### 2.1 Time Worked in Hours

Add the following formula to calculate time difference in Excel:

`=C5-B5`

To calculate the time worked in only hours, modify the formula:

`=(C5-B5)*24`

Excel will give you the result in time format. To change this, go to the **Numbers format** in the **Home **tab. Click on **Number**.

You will get the hours worked.

If you want the output in integer format instead of decimal format, use** the INT function**:

`=INT((C5-B5)*24)`

It will show hours worked in integer numbers.

### 2.2 Time Worked in Minutes

To estimate the time difference in minutes, multiply the times of that column by the total number of minutes in a day. That is, 1440 (24 hrs*60 min).

The formula:

`=(C5-B5)*24*60`

Excel will give the output in time format. Change the format as required from the Numbers group of the **Home **tab.

### 2.3 Time Worked in Seconds

To calculate the time difference in seconds, multiply the previous result by the total number of seconds in a day. That is, 86400 (24 hrs * 60 min * 60 sec).

Use the following formula:

`=(C5-B5)*24*60*60`

**Note: This formula will only work if you are calculating the Excel time difference for the same day. If your time values are from different dates, this formula will return incorrect output.**

**Read More:** How to Calculate Hours and Minutes for Payroll Excel

## Formula 3 – Calculate Time Worked Using Excel TEXT Function

With **the TEXT function**, you don’t have to worry about changing the format.

**The Generic Formula:**

**=TEXT(End Time – Start Time, Format)**

The first argument is basic subtraction. And in the format, enter the time difference format that you want.

### 3.1 Display Only Hours

To display only hours worked, use the following formula:

`=TEXT(C5-B5,"hh")`

This formula will only deliver the outcome that displays the number of hours difference between the two-time values. If your outcome is 10 hours and 40 minutes, it will display 9 hours only.

### 3.2 Display Only Minutes

To display only minutes worked, use the following formula:

`=TEXT(C5-B5,"[mm]")`

### 3.3 Display Only Seconds

To display only seconds worked, use the following formula:

`=TEXT(C5-B5,"[ss]")`

### 3.4 Display Hours and Minutes

To display hours and minutes worked, use the following formula:

`=TEXT(C5-B5,"[hh]:mm")`

### 3.5 Display Hours, Minutes, and Seconds

To display hours, minutes, and seconds worked, use the following formula:

`=TEXT(C5-B5,"hh:mm:ss")`

**Read More: **How to Calculate On Time Delivery Performance in Excel

## Formula 4 – Calculate the Time Worked Till Now in Excel

To calculate the time worked between the start time and the current time, use **the NOW function **instead of the End Time in the Difference column.

The **NOW** function returns the present date and time from your device. It does not accept any input argument.

**The Generic Formula:**

**Time Worked = NOW() – Start Time**

If the difference in time between the start time and the current time is greater than 24 hours, format the outcome to display the day with the time portion using the **TEXT **function**.**

**The Formula:**

`=TEXT(NOW()-B5,"dd hh:ss:mm")`

You can execute the same thing by modifying the custom formatting of the cell to display the day along with the time part.

Excel will automatically consider the day as 1st January 1990 if your start time only has the time portion.

For this reason, the **NOW **function will provide you with incorrect output while calculating the time worked. As mentioned, the resulting value would also have the total days that have passed since 1st Jan 1990.

To solve this, use the following formula:

`=NOW()- INT(NOW())-B5`

The **INT **function will clear the day part from the result produced by this function. It will use this to calculate the time difference.

The **NOW **function updates whenever you make a change in your Excel timesheet. But it does not rework in real time.

**Read More: **How to Calculate Production per Hour in Excel

## Formula 5 – Formula to Calculate Hours Worked for A Day Shift

The sample dataset below has some start and end times of some employees. Our goal is to calculate the time worked in hours.

Select **Cell E5 **and add the following formula:

`=MOD(D5-C5,1)*24`

Our formula contains **the MOD function **to calculate the time worked in hours in an Excel timesheet.

It calculates the total work hours.

**Read More: **How to Calculate Total Hours Worked in a Week in Excel

## Formula 6 – Formula To Calculate Time Worked for a Night Shift

The next sample dataset is of a night shift where employees start work at night and finish the next day.

So, the date changes along with the time.

To solve this we are using an Excel formula with** the IF function**.

Select **Cell E5 **and add the following formula:

`=IF((D5-C5)<0,1-(C5-D5),(D5-C5))`

We calculated the time worked using the formula. Another way to solve this is by using the **MOD **function.

Select **Cell E5 **and add the following formula:

`=MOD(D5-C5,1)*24`

This formula handles the negative time by utilizing the MOD function to “reverse” negative values to the demanded positive value.

**Note to Remember: This formula won’t work for a time greater than 24 hours.**

**Read More: **How to Calculate Billable Hours in Excel

## Formula 7 – Formula to Calculate Overtime in Excel

In this Excel timesheet, you can see the start time and end time, with the standard working hour being 8 hours. If anyone worked more than 8 hours, our formula will display that in the Overtime column. The Worked column, will only show the standard work time.

To calculate the usual time worked in a day, add the following formula in **Cell E5** and drag the fill handle icon:

`=IF((D5-C5)*24>$C$11,$C$11,(D5-C5)*24)`

If the employee has operated for more than 8 hours, the formula will only produce a max of 8 hours.

To calculate the overtime in a day, add the following formula in **Cell F5** and drag the fill handle icon:

`=IF((D5-C5)*24>$C$11,((D5-C5)*24)-$C$11,0)`

This formula basically extracts the extra hours after subtracting time in from time out.

**Read More: **Excel Formula for Overtime over 8 Hours

## How to Calculate Hours Worked in Excel Using 24-Hour Clock

Use the **MOD **function for this.

`=MOD(D5-C5,1)*24`

You can use any of the above formulas to calculate the time worked in an Excel timesheet for a 24-hour clock.

## How to Calculate Total Hours in a Week in Excel?

To calculate total hours for a week, use the **IF **function**,** the **MAX** function, and the **SUM** function.

Apply the following formula:

`=IF(SUM($E$5:E5)>40,SUM($E$5:E5)-40,0)`

This function calculates overtime if a person works more than 40 hours a week.

The role of the first range of the **SUM **function is absolute, but the second part is not. When you copy this formula across the column, you will witness that the SUM function sums up all the Hours operated in **Worked **column. When the SUM range increases, the hours worked will also increase. Once the **SUM** reaches more than 40 hours, it will put the overtime hours.

The regular hours are estimated based on the total hours, and the overtime operated:

`=MAX(E5-G5,0)`

We use the **MAX **function to not end up with Negative hours where the Employee has operated the overtime. If the formula returns a negative, the **MAX **function will return a zero in the Excel timesheet.

**Read More:** How to Calculate Hours Worked and Overtime Using Excel Formula

## How to Calculate Total Hours in a Month in Excel?

You can calculate the total time (hours) worked in a month using **the NETWORKDAYS function** in Excel.

**The Generic Formula:**

**=NETWORKDAYS(start date,end date)*working hour per day**

The Formula we used:

`=NETWORKDAYS(B5,C5)*8`

You can see the total hours worked in an entire month. We didn’t include holidays.

To get the total hours worked without holidays, the formula will be:

**=NETWORKDAYS(start date,end date,holiday_list)*working hour per day**

## Excel Formula to Calculate Hours Worked Minus Lunch

We can calculate hours worked minus the lunch time by using the **SUM **function.

**The Generic Formula:**

**=SUM((Lunch_start-start_time)+(end_time-lunch_end))*24**

Formula used for sample dataset:

`=SUM((D5-C5)+(F5-E5))*24`

## Things to Remember

**✎ **Make sure to change the time format to number or general if it is not showing in decimal format.

**✎** If the formula returns **####**, it means your value is negative or the column width is too small.

**Download Practice Workbook**

**Related Articles**

- Excel Formula to Calculate Overtime and Double Time
- Man Hours Calculation in Excel
- How to Create an Injection Molding Cycle Time Calculator in Excel

**<< Go Back to Calculate Total Time | Calculate Time | Date-Time in Excel | Learn Excel**