Excel Timesheet Formula with Lunch Break and Overtime

Get FREE Advanced Excel Exercises with Solutions!

If you are looking for some special tricks to create an Excel timesheet formula with a lunch break and overtime, you’ve come to the right place. There is one way to create an Excel timesheet formula with a lunch break and overtime. This article will discuss every step of this method to create an Excel timesheet formula with a lunch break and overtime. Let’s follow the complete guide to learn all of this.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article. It contains all the datasets in different spreadsheets for a clear understanding.


Step-by-Step Procedure to Create Timesheet with Lunch Break and Overtime with Excel Formula

In the following section, we will use one effective and tricky method to create an Excel timesheet formula with a lunch break and overtime.  Work hours and overtime are generally calculated based on a 40-hour work week (eight hours per day). Based on the 40 hours of a work week, my Excel template also calculates work hours and overtime. Overtime is calculated in this article according to the whole week, not according to the individual week. To create a more understandable timesheet, it is necessary to make a basic outline and calculations with formulas and calculate the overtime. This section provides extensive details on this method. You should learn and apply all of these to improve your thinking capability and Excel knowledge. We use the Microsoft Office 365 version here, but you can utilize any other version according to your preference.

Excel Timesheet Formula with Lunch Break and Overtime


Step 1: Create Primary Outline

To create a timesheet with lunch and overtime, we have to follow some specified rules. At first, we want to make a dataset. To do this we have to follow the following rules.

  • Firstly, write ‘Timesheet with Lunch Break and Overtime’ in some merged cells at a larger font size, That will make the heading more attractive. Then, type your required Headline fields for your data. Click here to see a screenshot that illustrates what the fields look like.

Create Primary Outline

  • Afterward, you need to enter the necessary information about the company for which timesheets are being made.
  • Then, you have to enter the address, phone no, and website of the company as shown below.
  • Following that, you need to enter the employee’s name, the project manager’s name, and today’s date.
  • Next, you have to enter Overtime Counted After (Hrs/Week) Generally, this is 40 hours per week. You should enter the number 45 or another value if your office follows that schedule. Working hours generally vary from country to country.

Excel Timesheet Formula with Lunch Break and Overtime


Step 2: Input Necessary Data in Timesheet

Now, after completing the heading part, you have to input the basic information of the timesheet. To do this, you have to follow the following procedures.

  • In the following image, we can see the basic outlines of the timesheet data and its related dataset.
  • Here, we have Day, Time In, Lunch Starts, Time Out Total Hours, Regular, and Overtime columns in the following dataset.
  • In the Time In column, we enter the time when the employee enters the working place.
  • Then, in the Lunch Starts column, we type the time when the lunch starts.
  • Next, in the lunch Ends column, we enter the lunch ends time.
  • Then, in the Time Out column, we type the time when the employee completely leaves the working place.

Input Basic Information of Timesheet

Read More: How to Create an Employee Timesheet in Excel (with Easy Steps)


Step 3: Calculate Total Hours for Each Day

Now, we are going to calculate the total hours for individual days. To do this, you have to follow the following steps.

  • First of all, to calculate total hours, we will use the following formula in the cell G11:

=((F11-C11)-(E11-D11))*24

  • (F11-C11) is actually indicates (Time Out-Time In).  Here, (E11-D11)  means (Lunch Ends-Lunch Starts).
  • Now, we multiplied the ((Time Out-Time In)(Lunch Ends-Lunch Starts)) formula by 24 to convert it into an hour.
  • Because of this, we get a number as a value. In the alternative, subtracting time results in a time value.
  • Then, press Enter.
  • As a consequence, you will get the total hours for Monday.

Calculate Total Hours of the Timesheet

  • Next, drag the Fill Handle icon to fill out the rest of the cells in the column with the formula.
  • Therefore, you will get the total hours for individual days, as shown below.

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


Step 4: Calculate Regular Hours and Overtime

Now, we are going to calculate regular hours and overtime. To calculate regular hours we will use the MAX function. Here, we also use the IF function to calculate overtime. We also use the SUM function to calculate the total hours of regular and overtime. To do this you have to follow the following rules.

  • First of all, to calculate total hours, we will use the following formula in the cell I11:

=IF(SUM($G$11:G11)>$G$8,SUM($G$11:G11)-$G$8,0)

  • Then, press Enter.
  • As a result, you will get overtime value for Monday.

Calculate Regular Hours and Overtime

  • Next, drag the Fill Handle icon to fill out the rest of the cells in the column with the formula.
  • Therefore, you will get the overtime for individual days, as shown below.

Excel Timesheet Formula with Lunch Break and Overtime

  • Next, to calculate regular hours, we will use the following formula in the cell H11:

=MAX(G11-I11,0)

  • Then, press Enter.
  • As a result, you will get regular hours value for Monday.

  • Next, drag the Fill Handle icon to fill out the rest of the cells in the column with the formula.
  • Therefore, you will get the regular hours value for individual days, as shown below.

Excel Timesheet Formula with Lunch Break and Overtime

  • Next, to calculate total hours( regular), we will use the following formula in the cell H17:

=SUM(H11:H16)

  • Then, press Enter.
  • As a consequence, you will get total hours (regular) as shown below.

  • Next, to calculate total hours(overtime), we will use the following formula in the cell I17:

=SUM(I11:I16)

  • Then, press Enter.
  • As a consequence, you will get total hours (overtime) as shown below.

Excel Timesheet Formula with Lunch Break and Overtime

🔎 How Does the Formula Work?

  • The expanding range of the above formula is $G$11:G11 which is applicable for I11. For the next cell I13, the range will be $G$11:G12. This is the reason it is called expanding range.
  • IF(SUM($G$11:G11)>$G$8,SUM($G$11:G11)-$G$8,0)

The IF function will check whether the sum of the expanding range has surpassed the cell G8 value (overtime calculator after) or not. If the value surpasses, the function will return the SUM($G$11:G11)-$G$8 value. Otherwise, the function will return a value of 0.

Read More: How to Calculate Overtime Hours in Excel Using IF Function


💬 Things to Remember

✎ When you use the IF function carefully give all the required parentheses. You should also make the range and [sum_range] absolute cell reference, otherwise, you will not get the proper value.

✎ You have to adjust row height after following each method.


Conclusion

That’s the end of today’s session. I strongly believe that from now you may be able to create a timesheet formula with a lunch break and overtime in Excel. If you have any queries or recommendations, please share them in the comments section below.

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

Saquib Ahmad Shuvo
Saquib Ahmad Shuvo

Welcome to my Profile. I am working on and researching Microsoft Excel right now, and I will be posting articles about it here. I received a B.Sc. in Naval Architecture and Marine Engineering from the Bangladesh University of Engineering and Technology (BUET). Having studied naval architecture, I have a strong interest in research and development. Always try to learn from different sources and come up with creative solutions.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo