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?
In your sample workbook, Luk checks in on 2021/02/01 but doesn't check out. The same for Kho on 2021/02/02, etc.
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.
