Forum Discussion
Calculate Total Hours Worked / Night Differential of Multiple Personnel having Different Shifts
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.
- Got it. No worries. Thank you for your time to reply.
