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.

Download Practice Workbook

Formula to Find Time Differences in Excel

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 doubt. I recommend you learn and apply all these to your excel timesheet to calculate the time difference.

1. Time Worked Using Simple Subtraction

Now, the simple formula to calculate the time difference is to subtract start time 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 data, 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 are successful in calculating the time worked after performing a subtraction in the Excel timesheet.

Read More: How to Subtract Time in Excel (7 Quick Methods)

2. Compute Time Worked in Hours, Minutes, or Seconds

In the previous example, you saw our output was formatted 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 result in decimal but want them in integer format, use the INT function like the following:

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

Read More: How to Calculate Total Hours in Excel (9 Easy Methods)

2.2 Time Worked in Minutes

To estimate the time contrast in minutes, you require 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.

Read More: How to Add Minutes to Time in Excel (3 Quick Methods)

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 Time in Excel (16 Possible Ways)

3. Calculate Time Worked Using the 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 tome 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.

Read More: Timesheet Formula in Excel (5 Examples)

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.

Related Content: How to Subtract and Display Negative Time in Excel (3 Methods)

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.

4. Time Worked Till Now

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

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 time 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.

Formula to Calculate Time Worked for a Night Shift

Now, previously discussed the time difference 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.

Similar Readings:

• How to Calculate Elapsed Time in Excel (8 Ways)
• Calculate Elapsed Time Between Two Dates in Excel (5 Methods)
• How to Subtract Military Time in Excel (3 Methods)
• Use Time Format in Excel VBA (Macro, UDF, and UserForm)
• How to Calculate Average Handling Time in Excel (2 Easy Ways)

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 that extra hours after subtracting time in from the time out in the Excel timesheet.

Read More: Excel Formula for Overtime over 40 Hours [with Free Template]

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 formula to calculate the time worked in an Excel timesheet for a 24-hour clock.

Read More: How to Add Time in Excel Over 24 Hours (4 ways)

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 Total Hours in Excel (9 Easy Methods)

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 hour 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 total hours worked without holidays, the formula will be:

To know more about this function, read: How to Use NETWORKDAYS Function in Excel (3 Suitable Examples)

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 have to check in and check out every workday, measuring the whole worked period and minus the lunch of a day can assist to figure out salary according to the time. From the example, I showed the formula to calculate the time worked but minus lunch-time for each day in an Excel timesheet.

Read More: Excel Formula to Calculate Hours Worked Minus Lunch

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

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.

Don’t forget to check our website Exceldemy.com for various Excel-related problems and solutions.

Keep learning new methods and keep growing!

Related Articles

• [Fixed!] SUM Not Working with Time Values in Excel (5 Solutions)
• How to Calculate Turnaround Time in Excel (4 Ways)
• Calculate Hours and Minutes for Payroll Excel (7 Easy Ways)
• How to Sum Time in Excel (9 Suitable Methods)
• Calculate Hours Between Two Times in Excel (6 Methods)