How To Calculate Working Hours in Excel?
In this age of fast technology, everything has become very much easy with the help of internet. A task that would take out hours to get completed can now be done within a few minutes. And same is with calculating time, now we can easily find or calculate time effortlessly.
is a wonderful tool that has wonderfully increased humans productivity. Excel has a vast number of formulas that help us do anything related to calculations. If you are looking for a solution to your problem of finding the time between two dates, then I am pretty sure that you just arrived at the right place.
By using Excel formulas,
one can easily find the difference in hours by adding a time specifier to the formula by altering the cell format in your spreadsheet. Excel lets you apply your own formulas to multiple cells
at the same time simply by clicking the corner of your primary formula cell
and dragging it with the mouse. All you need to have is a Start time and an End time, excel will count the hours between them within a very short interval.
Steps to find the time between two dates.
First of all, open your spreadsheet and in the title insert "In" in the cell A1 and "Out" in the cell B1.
Enter the start time in cell A2 and End time in the cell B2.
Now Enter the title "Total Hours"
in cell C1. Select cell C2 by clicking on it with your mouse.
- Now enter the formula "=B2-A2" and press the enter key to show the time difference between two of the cells. You can change the cell format to hours by selecting the cell and clicking on the arrow next to the "Number" Option, which is the "Home" tab.
- Select the "Custom" option and type "h" into the "Type" field to change the cell format to hours. Now enter the formula "=TEXT(B2-A2, "h")" to do this directly within the formula.
Count the total number of hours worked based on a single time unit by entering the formula "=INT((B2-A2)*24)"
and then press the Enter
key, the INT function will let you count the total number of hours between two times and will round the total to the nearest hour.
How To Calculate Hours Worked And Minus Lunch Time?
If you are doing a job, and you go in and out all day. You probably would be worried about the number of working hours. If you are worried about the counting of working hours whenever you go out for a lunch. Don't worry, I am gonna tell you how to calculate hours worked minus your lunch time.
For example, someone has the daily attendance record which contains your login, log out, start time of the lunch, ending time of the lunch and you want to calculate the total hours worked but minus the lunchtime every day you went for it. I have collected some formulas that will help you solve this problem.
The following formulas will help you to calculate worked time a day based on the time records minus the lunch time.
Enter this formula: =SUM((C2-B2)+(E2-D2))*24
into a blank cell beside your time record cells, F2
, see the below screenshot for better understanding.
Now drag the fill handle down to the cells that you want to apply this formula, and all the total hours worked excluding the lunchtime are calculated at once. See the screenshot below:
Template For Calculating Hours Worked
You can easily find the number of hours worked by Following these steps:
In A1, enter Time In.
In B1, enter Time Out.
In C1, enter Hours Worked
Now Select A2 and B2, and press [Ctrl]1
to open the Format Cells dialog box.
On the Number tab, select Time from the Category list box, choose 1:30 PM from the Type list box
, and click OK.
and select Format Cells.
On the Number tab, select Time from the Category list box, choose 13:30
from the Type list box, and click OK.
Now lastly,In C2
, enter the following formula:
Calculate Excel Formula To Calculate Hours Worked and Overtime(With Formula)
If you are looking for a way out to calculate the hours worked and overtime, then follow the steps below with screenshots to know how to do this by using simple Excel formulas.
In cell F1, enter this formula =IF((((C2-B2)+(E2-D2))*24)>8,8,((C2-B2)+(E2-D2))*24)
, and drag the auto fill handle down to display the regular working hour of each day. See screenshot below: