Saquib Ahmad Shuvo
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.
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.
- 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.
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.
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.
- 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.
- 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.
- 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.
- 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.
🔎 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
- Create a Monthly Timesheet in Excel (with Easy Steps)
- How to Create a Weekly Timesheet in Excel (with Easy Steps)
- Excel Formula for Overtime over 8 Hours (4 Examples)
- How to Calculate Overtime Percentage in Excel (3 Quick Methods)
- Excel Formula to Calculate Overtime and Double Time (3 Ways)
- How to Calculate Overtime over 40 Hours Using Excel Formula
