Every employer would like to track the hours worked by his employees. However, this may be challenging and time-consuming if manual methods are used. Luckily, Excel allows its users to calculate the working hours for someone using some in-built functions. This has greatly simplified the process of tracking employees' working duration.
Below are some tips and methods to calculate working hours in Excel.
Calculating work hours using simple Excel Formula
Excel has numerous formulas that can be used to calculate working hours. However, there's a simple step to do so;
1. To get started, open your Excel application.
2. Then, open an existing or a new document. If you're working on an existing document, you can open it from your Pc or use the keyboard shortcut to access it (Ctrl + O).
3. Enter the dataset of your employees on the empty cells.
4. To change the time format, follow these steps;
- Highlight all the datasets that contain the time values.
- Then, right-click to open a side-view menu.
- On the side-view menu, click on the "Format cells" button.
5. Click on the first cell, within the Time Worked column, and then, type this formula
=(C2-B2)*24
6. Hit the Enter button and you will now have the time difference in hours. Drag the formula downwards to apply to other cells.
Calculating Overtime work hours in Excel
In many cases, the worker exceeds the time out and works for more hours or minutes. Here is how to calculate overtime in Excel.
1. Enter the dataset of your employees on the empty cells. Since you're calculating over time you need to have standard working hours. In our case, let the standard working hours be 6 hours.
2. Click on the first cell, within the Overtime column, and then, type this formula
=IF((C2-B2)*24>$F$13,((C2-B2)*24)-$F$13,0)
3. Hit the Enter button and you will now have the time difference in hours. Drag the formula downwards to apply to other cells.
4. By doing so, you will have the overtime hours in the Overtime column.
Using MOD function
In a case where the working time shift spans midnight, it is impossible to use the already discussed methods. However, Excel has an in-built function, MOD, that can be used to calculate such working hours.
Here are the steps to use this function;
1. After opening a new worksheet, enter the dataset of your employees on the empty cells. Alternatively, you can use an existing document.
2. Then, Click on the first cell within the Time worked column.
3. On the main screen, click on the "formulas" tab and then choose the "Insert Function" button.
4. On the dialogue box, search for "MOD" and click the "Ok" button.
5. On the Function Arguments dialogue box, type this formula;
=MOD(C2-B2)*24
6. Drag the formula to other cells within the Time worked column to apply changes.
Therefore, using any of the above methods, you can easily track the attendance schedule of your workers.