Excel Formula To Calculate Time Worked

In Microsoft Excel, working with time is one of the familiar tasks. You will find yourself in a lot of situations where you will have to calculate time differences. If you are in a managing position in your company, you will have to track down all the employees’ timesheets. You have to calculate how many hours they have worked. In this tutorial, you will learn to calculate the time worked using the Excel formula with suitable examples and proper illustrations.

In many situations, you will have to calculate time differences or time worked between two times in a timesheet. To calculate the total time worked, you have to know the basic formula for calculating time differences. In the following sections, I will show you some simple and easy examples that will clear your doubts. I recommend you learn and apply all these to your Excel timesheet to calculate the time difference.

1. Excel Subtraction Formula To Calculate Time Worked

Now, the simple formula to calculate the time difference is to subtract the start time from to end time. In this way, you can compute the time worked for any shift in your Excel timesheet.

Take a look at the following screenshot:

Here, we have some time differences. The formula we used:

The problem you can notice here is we have a time difference in time format. As for the first date, we wanted 8 hours. Instead, we got 8:00 AM.

You can format it in different ways. You have to just change it to Custom format to get the result in hours, minutes, and seconds format.

To change this follow these steps:

• First, select the range of cells.

• Now, press Ctrl+1 on your keyboard.

• Now, from the Format Cells dialog box, you will find various Numbers Next, select Custom from Category. Then, from the Type, select h:mm: ss format. Finally, click on OK.

In the end, it will display the time worked in hours, minutes, and seconds format. So, we were successful in calculating the time worked after performing a subtraction in the Excel timesheet.

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

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

In the previous example, you saw our output was formatted in time hours, minutes, and seconds format. Now, you may want to calculate work hours in minutes or seconds format. You can also compute the time worked using the Excel formula. To demonstrate this, we are using the previous dataset.

2.1 Time Worked in Hours

You already saw the following formula to calculate time difference in Excel:

Now, to calculate the time worked in only hours, modify the formula like the following:

Again, Excel will give you the result in time format. To change this, go to the Numbers format in the Home tab. From there click on Number.

After that, you will get the hours worked in your Excel timesheet.

Now, if you don’t want the results in decimal but want them in integer format, use the INT function like the following:

As you can see, we are successful in calculating hours worked in an Excel timesheet.

2.2 Time Worked in Minutes

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

The formula:

Excel will again give them in time format. So, change that from the Numbers group of the Home tab.

2.3 Time Worked in Seconds

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

We are using the following formula:

In this way, you can calculate the time worked in any format using this formula for an Excel timesheet.

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

3. Calculate Time Worked Using Excel TEXT Function

In the previous section, we had to change the format of the time difference to calculate. Because Excel automatically changes the difference in time format. For this reason, we had to change the time format.

Now, if you don’t want to face this hazard and want a simple solution, use the TEXT function. Here, you don’t have to worry about changing the format.

The Generic Formula:

Now, the first argument is basic subtraction. And in the format, you have to just enter the time difference format that you want.

3.1 Display Only Hours

To display only hours worked, use the following formula:

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:

3.3 Display Only Seconds

To display only seconds worked, use the following formula:

3.4 Display Hours and Minutes

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

3.5 Display Hours, Minutes, and Seconds

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

Now, you may ask why we are using square brackets like [hh],[mm], or [ss] in somewhere. Basically, it gives you the whole number of hours worked between the two dates, even if the hour is greater than 24. So if you want to calculate hours worked between two date values where the distinction is more than 24 hours, utilizing [hh] will deliver you the total number of hours worked, and “hh” will just give you the hours passed on the day of the end date.

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

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 the time from your device. It does not accept any input argument.

The Generic Formula:

Have a look at the following screenshot for a better understanding:

If the distinction 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:

Now, you can also 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 time 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 we mentioned, the resulting value would also have the total days that have passed since 1st Jan 1990.

To solve this, use the following formula:

Here, the INT function will clear the daypart from the result produced by this function. After that, it will use this to calculate the time difference.

Remember, 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

5. Formula to Calculate Hours Worked for A Day Shift

In this section, I am showing you a simple dataset that has some start times and the end times of some employees. Our goal is to calculate the time worked in hours.

To demonstrate this, we are using this dataset:

Now, select Cell E5 and type the following formula:

Here, our formula contains the MOD function to calculate the time worked in hours in an Excel timesheet.

As you can see, we are successful in calculating total work hours in the Excel timesheet.

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

6. Formula To Calculate Time Worked for a Night Shift

Now, as previously discussed the time difference is based on the day shift. That means we didn’t show you the output if your end time is on a different date. That means your start time is in hh:mm: ss PM, but the end time is hh:mm: ss AM. You can compare this to a night shift where employees start work at night and finish the next day.

Have a look at the following dataset:

Here, you can clearly see some people worked at night. So, the date changed here also with the time.

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

Now, select Cell E5 and type the following formula:

As you can see, we calculated the time worked using the formula. Another useful way to solve this is using the MOD function to calculate the time worked in hours.

Select Cell E5 and type the following formula:

This formula handles the negative time by utilizing the MOD function to “reverse” negative values to the demanded positive value. Because this formula will endure times on the exact day and times that pass midnight, we don’t require to use an IF function. This is the beauty of the MOD function.

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

Read More: How to Calculate Billable Hours in Excel

7. Formula to Calculate Overtime in Excel

In this section, I will show you an example of overtime.  Overtime refers to any hours operated by an employee that surpasses their usually scheduled working hours.

Have a look at the dataset:

In this Excel timesheet, you can notice employees’ start time and end time. Here, our standard working hour is 8 hours. So, if anyone worked more than 8 hours, our formula will display that in the Overtime column. But, the Worked column, will only show the standard work time performed by the employee.

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

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, type the following formula in Cell F5 and drag the fill handle icon:

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

Read More: Excel Formula for Overtime over 8 Hours

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

Now, if you are using the 24-hour clock in your region, you can also previous formulas to perform this.

We are using the MOD function to perform this.

You can use any 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?

In this example, I will show you an example that will have total hours and overtime worked in a week by an employee. We are performing this using the IF function, the MAX function, and the SUM function.

Have a look at the following screenshot:

Here, we calculated the time overtime worked by the following formula:

This function basically calculates overtime once a person performs 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 into the Overtime column as an increasing total.

Now, have a look at the following screenshot:

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

We utilize the MAX function to not end up with Negative hours where the Employee has operated the overtime. If the formula returns a negative, then 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.

Basically, this function calculates the total hours worked based on your company’s working hours.

The Generic Formula:

Take a look at the following screenshot:

The Formula we are using:

Here, you can see the total hours worked in an entire month. We didn’t include holidays here.

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

Excel Formula to Calculate Hours Worked Minus Lunch

Now, you may have an Excel timesheet where you have to consider the lunchtime of the employees. And you can’t add this to the working hours to calculate. We can calculate hours worked minus the lunch by using the SUM function.

The Generic Formula:

Take a look at the following screenshot to have a better understanding:

We are using this formula:

As business employees, we need to check in and check out every workday, measuring the whole worked period minus the lunch of a day can assist in figuring out salary according to the time. From the example, I showed the formula to calculate the time worked minus lunch time for each day in an Excel timesheet.

💬 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 smaller.

Download Practice Workbook

Conclusion

To conclude, I hope this tutorial has provided you with a piece of useful knowledge to use the formula to calculate the time worked in Excel. We recommend you learn and apply all these instructions to your dataset. Download the practice workbook and try these yourself. Also, feel free to give feedback in the comment section. Your valuable feedback keeps us motivated to create tutorials like this.

Keep learning new methods and keep growing!

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