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.)
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.