Forum Discussion
How calculate working hours
See the attached version. I used formulas that should work in all versions of Excel. If you have Excel 2019 or Excel in Microsoft 365, they could be simplified by using MINIFS and MAXIFS.
What do the times in your table mean?
| E | TIME |
| 2021/02/01 | 7:36:00 AM (Clock In) |
| 2021/02/01 | 8:39:00 AM (Clock In again) |
| 2021/02/01 | 5:01:19 PM (Clock out) |
| 2021/02/02 | 7:03:07 AM |
| 2021/02/02 | 7:18:54 AM |
| 2021/02/02 | 8:02:42 AM |
| 2021/02/02 | 8:32:28 AM |
| 2021/02/02 | 5:03:20 PM |
| 2021/02/02 | 5:53:42 PM |
Above the table is an employee's attendance record. However, the standard of working hours in our company starts from 8 am till 5 pm. The lunch hour will be deducted for 1 hour. You may see the example above, there is the double clock in which are 7:36 am and 8:39 am. Then, I'd like to calculate it start from 7:36 am but not 08:39 am and end at 5:01:19 pm. Now, I don't have an idea of how to formulate it. Hopefully, professor Excel could help me solve this problem.
- Another one is there is flexible time for employees . For the sample above, how to calculate working hours start from 07:36am till 5:01:19 and deducted for 1 hour luchbreak?
See the attached version. I used formulas that should work in all versions of Excel. If you have Excel 2019 or Excel in Microsoft 365, they could be simplified by using MINIFS and MAXIFS.
- Thanks for the prompt reply. And thanks again for helping me formulate the working time.