Discussion Re: How calculate working hours in Excel
https://techcommunity.microsoft.com/t5/excel/how-calculate-working-hours/m-p/2462102#M103711
<P><LI-USER uid="1081085"></LI-USER> In case you are not on a Mac, using Excel 2013 or later and interested in a solution that doesn't involve a large number of formulae, perhaps Power Query is something for you.</P><P> </P><P>I looked at the file you uploaded ("sample 1"), and notice you have data for several employees and months. I assume that the data will grow over time and that you want to summarise the working hours by employee/month (or perhaps by week). Then it would be easier to clean-up the time records and transform them into a neat table (via Power query) that can be the basis for a pivot table. I also noticed several instances where employees checked IN, but not OUT. Or IN twice but not OUT. Or OUT but not IN. Obviously, these entries will cause errors in an automated solution, with out extra measures. And I may not have spotted them all.</P><P> </P><P>For what it's worth, the attached workbook takes <STRONG>your</STRONG> data into Power Query and presents a pivot table, summarising working hours (OUT -/- IN -/- 1 hour for lunch) per employee/month. It will take some learning, but certainly worth it if this is a recurring task involving large data sets. Set it up properly <STRONG>once</STRONG> and it will take a few seconds to refresh, whenever you need.</P>Fri, 18 Jun 2021 09:36:14 GMTRiny_van_Eekelen2021-06-18T09:36:14ZHow calculate working hours
https://techcommunity.microsoft.com/t5/excel/how-calculate-working-hours/m-p/2456712#M103520
<P>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. </P><TABLE border="0" width="452" cellspacing="0" cellpadding="0"><TBODY><TR><TD width="312" height="29">DATE</TD><TD width="140">TIME</TD></TR><TR><TD height="29">2021/02/01</TD><TD>7:36:00 AM</TD></TR><TR><TD height="29">2021/02/01</TD><TD>8:39:00 AM</TD></TR><TR><TD height="29">2021/02/01</TD><TD>5:01:19 PM</TD></TR><TR><TD height="29">2021/02/02</TD><TD>7:03:07 AM</TD></TR><TR><TD height="29">2021/02/02</TD><TD>7:18:54 AM</TD></TR><TR><TD height="29">2021/02/02</TD><TD>8:02:42 AM</TD></TR><TR><TD height="29">2021/02/02</TD><TD>8:32:28 AM</TD></TR><TR><TD height="29">2021/02/02</TD><TD>5:03:20 PM</TD></TR><TR><TD height="29">2021/02/02</TD><TD>5:53:42 PM</TD></TR></TBODY></TABLE><P> </P>Thu, 17 Jun 2021 07:16:54 GMThttps://techcommunity.microsoft.com/t5/excel/how-calculate-working-hours/m-p/2456712#M103520ANNABELLABLOG2021-06-17T07:16:54ZRe: How calculate working hours
https://techcommunity.microsoft.com/t5/excel/how-calculate-working-hours/m-p/2457258#M103559
<P><LI-USER uid="1081085"></LI-USER> </P>
<P>What do the times in your table mean?</P>Thu, 17 Jun 2021 09:39:10 GMThttps://techcommunity.microsoft.com/t5/excel/how-calculate-working-hours/m-p/2457258#M103559Hans Vogelaar2021-06-17T09:39:10ZBetreff: How calculate working hours
https://techcommunity.microsoft.com/t5/excel/how-calculate-working-hours/m-p/2458068#M103598
<P><LI-USER uid="1081085"></LI-USER> </P><P>Here is a small approach, hope that helps.<BR />Otherwise, as Mr. Hans Vogelaar has already written to you, please explain in more detail.</P><P> </P><P> </P><P>I would be happy to know if I could help.</P><P> </P><P> </P><P>Nikolino</P><P>I know I don't know anything (Socrates)</P><P>* Kindly Mark and Vote this reply if it helps please, as it will be beneficial to more Community members reading here.</P>Thu, 17 Jun 2021 13:35:16 GMThttps://techcommunity.microsoft.com/t5/excel/how-calculate-working-hours/m-p/2458068#M103598NikolinoDE2021-06-17T13:35:16ZRe: How calculate working hours
https://techcommunity.microsoft.com/t5/excel/how-calculate-working-hours/m-p/2461618#M103694
<P><LI-USER uid="127945"></LI-USER> </P><TABLE border="0" width="452" cellspacing="0" cellpadding="0"><TBODY><TR><TD width="312" height="29">E</TD><TD width="140">TIME</TD></TR><TR><TD height="29">2021/02/01</TD><TD>7:36:00 AM (Clock In)</TD></TR><TR><TD height="29">2021/02/01</TD><TD>8:39:00 AM (Clock In again)</TD></TR><TR><TD height="29">2021/02/01</TD><TD>5:01:19 PM (Clock out)</TD></TR><TR><TD height="29">2021/02/02</TD><TD>7:03:07 AM </TD></TR><TR><TD height="29">2021/02/02</TD><TD>7:18:54 AM</TD></TR><TR><TD height="29">2021/02/02</TD><TD>8:02:42 AM</TD></TR><TR><TD height="29">2021/02/02</TD><TD>8:32:28 AM</TD></TR><TR><TD height="29">2021/02/02</TD><TD>5:03:20 PM</TD></TR><TR><TD height="29">2021/02/02</TD><TD>5:53:42 PM</TD></TR></TBODY></TABLE><P> </P><P>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.</P><P> </P>Fri, 18 Jun 2021 06:02:49 GMThttps://techcommunity.microsoft.com/t5/excel/how-calculate-working-hours/m-p/2461618#M103694ANNABELLABLOG2021-06-18T06:02:49ZRe: How calculate working hours
https://techcommunity.microsoft.com/t5/excel/how-calculate-working-hours/m-p/2461654#M103696
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?Fri, 18 Jun 2021 06:19:51 GMThttps://techcommunity.microsoft.com/t5/excel/how-calculate-working-hours/m-p/2461654#M103696ANNABELLABLOG2021-06-18T06:19:51ZRe: How calculate working hours
https://techcommunity.microsoft.com/t5/excel/how-calculate-working-hours/m-p/2461696#M103701
<P><LI-USER uid="127945"></LI-USER> </P><P> </P><P>Kindly open the document attached. Thank you so much.</P>Fri, 18 Jun 2021 06:56:00 GMThttps://techcommunity.microsoft.com/t5/excel/how-calculate-working-hours/m-p/2461696#M103701ANNABELLABLOG2021-06-18T06:56:00ZBetreff: How calculate working hours
https://techcommunity.microsoft.com/t5/excel/how-calculate-working-hours/m-p/2461703#M103703
<P><LI-USER uid="722750"></LI-USER> </P><P> </P><P>Kindly open the file as attached. Thank you so much.</P>Fri, 18 Jun 2021 06:58:18 GMThttps://techcommunity.microsoft.com/t5/excel/how-calculate-working-hours/m-p/2461703#M103703ANNABELLABLOG2021-06-18T06:58:18ZRe: How calculate working hours
https://techcommunity.microsoft.com/t5/excel/how-calculate-working-hours/m-p/2461705#M103704
<P><LI-USER uid="1081085"></LI-USER> </P>
<P>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.</P>Fri, 18 Jun 2021 07:03:40 GMThttps://techcommunity.microsoft.com/t5/excel/how-calculate-working-hours/m-p/2461705#M103704Hans Vogelaar2021-06-18T07:03:40ZRe: How calculate working hours
https://techcommunity.microsoft.com/t5/excel/how-calculate-working-hours/m-p/2461958#M103707
Thanks for the prompt reply. And thanks again for helping me formulate the working time.Fri, 18 Jun 2021 08:50:33 GMThttps://techcommunity.microsoft.com/t5/excel/how-calculate-working-hours/m-p/2461958#M103707ANNABELLABLOG2021-06-18T08:50:33ZRe: How calculate working hours
https://techcommunity.microsoft.com/t5/excel/how-calculate-working-hours/m-p/2462102#M103711
<P><LI-USER uid="1081085"></LI-USER> In case you are not on a Mac, using Excel 2013 or later and interested in a solution that doesn't involve a large number of formulae, perhaps Power Query is something for you.</P><P> </P><P>I looked at the file you uploaded ("sample 1"), and notice you have data for several employees and months. I assume that the data will grow over time and that you want to summarise the working hours by employee/month (or perhaps by week). Then it would be easier to clean-up the time records and transform them into a neat table (via Power query) that can be the basis for a pivot table. I also noticed several instances where employees checked IN, but not OUT. Or IN twice but not OUT. Or OUT but not IN. Obviously, these entries will cause errors in an automated solution, with out extra measures. And I may not have spotted them all.</P><P> </P><P>For what it's worth, the attached workbook takes <STRONG>your</STRONG> data into Power Query and presents a pivot table, summarising working hours (OUT -/- IN -/- 1 hour for lunch) per employee/month. It will take some learning, but certainly worth it if this is a recurring task involving large data sets. Set it up properly <STRONG>once</STRONG> and it will take a few seconds to refresh, whenever you need.</P>Fri, 18 Jun 2021 09:36:14 GMThttps://techcommunity.microsoft.com/t5/excel/how-calculate-working-hours/m-p/2462102#M103711Riny_van_Eekelen2021-06-18T09:36:14ZRe: How calculate working hours
https://techcommunity.microsoft.com/t5/excel/how-calculate-working-hours/m-p/2462161#M103712
<P><LI-USER uid="1081085"></LI-USER> </P>
<P>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.</P>Fri, 18 Jun 2021 10:00:48 GMThttps://techcommunity.microsoft.com/t5/excel/how-calculate-working-hours/m-p/2462161#M103712Hans Vogelaar2021-06-18T10:00:48Z