Forum Discussion
Solved
Calculate Total Hours Worked / Night Differential of Multiple Personnel having Different Shifts
Hello everyone! First time to post here. 🙂 Been working on this sheet on and off for two weeks now and i just cant get the right formula for this requirement.
I'm running a store that has at least 10 personnel that is working on different shifts. (Please see below existing template)
As you can see, i have one sheet for multiple personnel. Here is what i wish to accomplish.
1. To get total hours work based on the start of shift time and not on the clocked in by the personnel.
2. Night differential if personnel reaches 10pm to 6am of work
3. Number of late in minutes. Late clock-in from the start of shift and late clock-in from the number of allotted break time.
I've filled up the first row just to show the proper figure to display there after computation.
*Shift schedule is 2pm to 12am. That is 10hr shift less the 2 hr break time. hence, 8hr work shift
*Shift ended at 12:16am. Personnel acquires night differential as he worked from 10pm to 12am. hence, 2 hrs ND.
*2pm to 10pm shift starts. Personnel clocked in at 2:09pm. hence, the 9-minute late. He was able to clock-in before allotted break time. so late is just coming from the start of shift.
***Note: Personnel Time-in and out are downloaded from biometrics system. i just copy and paste to excel. 🙂
Hope someone can shed a light if this is something workable in excel.
As I noted in the workbook, that solution requires Excel 365 or Excel for the web.
Microsoft ended mainstream support for Excel 2007 in October 2012 (and ended extended support in October 2017). Consider getting a newer version, or switching to an alternative product; more than one are free.
The new attached workbook contains alternative formulas. These formulas are not as easy to understand. Microsoft's online documentation no longer identifies what functions were available in Excel 2007, so I am guessing that the functions in these formulas were all supported in Excel 2007.
Still, you could be explicit in how Night Differential is to be calculated. My notes in the new workbook on this calculation are slightly different. (Other formula notes are also different, as different functions were used.)
Hope someone can shed a light if this is something workable in excel.
It's very definitely something that is workable in Excel. But you will need to be quite a bit more expansive on how to deal with the details in some of those times.
For example, you're very careful to note the 9 minutes late for clocking in on that first row; but you don't give any credit for the 16 minutes of overtime (leaving after midnight). Now, maybe you want to subtract the 9 from the 16 to give credit for only 7 minutes....but you don't seem to note it at all.
And so on for each of the little deviations from exactly two hours of break time, and the same for each of the little deviations from exactly 8 hours of work time.
Are you content with rounding the times to whole hours? Rounding only if less than "xx" minutes? And so on.
What's the point of using Excel here if you aren't going to pay attention to the details. The mere fact that each of the several alternative schedules allows exactly 10 hours, minus an apparently automatic** 2 hours for break, makes me wonder why you're tracking things to the closest minute.
(**your example, at least, looks as if you're handling breaks as an automatic 2 hours; what would happen if a person only took, say, 90 minutes of break, or some other significantly shorter time?)
It's easy to come up with formulas to do what you've shown in that first row (except for my question about overtime for the 12:16 clocking out).....but it would help if you fill in all of the example rows, addressing my questions along the way.
And since you clearly have created a spreadsheet with this raw data (in order to create the image) might I ask you to post a copy of that actual spreadsheet? Put it on OneDrive or GoogleDrive, with a link pasted here that grants access to it. The formulas you're seeking are easy, as I've said, but greater clarity on the desired outcomes is needed first.
Thanks for the reply.
To answer your question, I would say the way one handles personnel's DTR varies. We follow a shift schedule and the start of personnel's shift is the period that it will be count as paid hour. Say if they clock in early, that wouldn't matter as they are obliged to be at work before their shift. Otherwise, they will be marked as late. The same applies during break time. they are allowed to have a break but ensure they'll be able to clock in before the allotted number of BT hours.
"it's easy to come up with formulas to do what you've shown in that first row (except for my question about overtime for the 12:16 clocking out).....but it would help if you fill in all of the example rows, addressing my questions along the way."
- Regarding this one, clocking out at 12:16 doesn't automatically mark the 16min as overtime. From where i am, overtime is not automatic paid time. it is always for approval basis and should always be justified. 😞 That is why it will be inputted manually in case.
As for the link of the spreadsheet, https://docs.google.com/spreadsheets/d/19-4zlCWkhymS6aNqCl38gjxf6b9I54b4/edit?usp=drive_link&ouid=113333825476291531572&rtpof=true&sd=true. 🙂
Again, Thank you for your help. 🙂
From what you've said in response to my questions, I don't think that you need formulas at this time. What you need is a clear definition of the purpose for which you're storing each of these pieces of information and how they're to be computed (IF, that is, computation is what you want. As it is, you're entering manually numbers that don't agree with the specific data elements, for whatever reason ("management approval needed" might be one of them, "routine entry" might be another). But whatever, until full AI is employed, AI that can read you mind and the mind of those who oversee this, I'm not the person to help; I'd want some better rules articulated so as to write reliable formulas. I wish I could, and if we were sitting down face-to-face I'd be asking a lot more probing questions, but we're not, so I can't. Best wishes.
By the way, you should remove that link because it's pretty clear you posted the spreadsheet with real names, not fake ones. Or go in and erase them and post a copy without any names. That's one of the rules of this forum.
Thank you for this. Truly appreciate your help. However, when i tried changing details just to check if it will change figures on the relevant cells, i encountered #name? in the said cells.
Also, will it be better if i separate the shift schedule start and end time and not be in one cell? e.g. from 2pm to 10pm in one cell, ill make it 2pm in one cell then 10pm on the next one.
"when i tried changing details just to check if it will change figures on the relevant cells, i encountered #name? in the said cells."
If you want assistance regarding that error, post one of the formulas that you changed. I may be able to spot an unintentionally changed function name or LET variable name, or an invalid cell reference that Excel thinks is a name. (Alternatively, the #NAME? error could indicate that you are using an older version of Excel that does not support the TEXTBEFORE and TEXTAFTER functions. But if that were the case, I expect that you would have seen the error indication immediately upon opening the workbook, before changing the formula. Which version of Excel are you using?)
"will it be better if i separate the shift schedule start and end time"That depends on what you mean by "better". Will it take less time to recalculate? Yes, but only a trivial amount. Will it take up less space in the workbook? Probably, but only by a small amount. Will it be more flexible, allowing you to put highlighting and/or validation on just one of the two times? Yes. Will it make data entry more prone to errors? You are the person that would best know, as it depends on how you are getting the data into the spreadsheet. I don't have a crystal ball that tells me what you are asking, or what your computing situation is, or what opportunities/problems you might encounter in the future.
But looking at the spreadsheet that you posted on Google Drive, I can say that by putting multiple dates across the width of the spreadsheet, you are making it more difficult to get multi-day summaries and do certain other analyses. The obvious alternative is to store only one date per row, and include a column containing the date (not just a day number), repeating employee names as needed.
