Forum Discussion
How calculate working hours
Hi, can you all please tell me how to calculate the total working hours? The start time is 8AM, End Time is 5PM. Deduct lunchtime for 1 HOUR.
| DATE | TIME |
| 2021/02/01 | 7:36:00 AM |
| 2021/02/01 | 8:39:00 AM |
| 2021/02/01 | 5:01:19 PM |
| 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 |
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.
10 Replies
Here is a small approach, hope that helps.
Otherwise, as Mr. Hans Vogelaar has already written to you, please explain in more detail.I would be happy to know if I could help.
Nikolino
I know I don't know anything (Socrates)
* Kindly Mark and Vote this reply if it helps please, as it will be beneficial to more Community members reading here.
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?
