I have a base I’m using to build an equalization table. It has to do with providing fair overtime based on overtime worked and opportunities not worked. I thought I had the sheet working well, until today when my boss threw a wrench in the works. I have to subtract where a normal shift overlaps an overtime shift to get total overtime worked.
One table contains employee data, including their normal shift start and end times. The other table contains details of an overtime shift that was worked. If I have a user who works 7A to 3:30P, but got called in for an overtime shift, and he worked 5A to 7:30P, I need to make it so the formula will automatically subtract the normal shift time from the shift worked. (I know, this one is easy, it’s 8 hours).
Not all employees work the same shifts, and I need to be able to automatically subtract whatever part of their regular shift overlaps the shift that was actually worked. There are also times when the overtime shift worked would actually coincide with the scheduled shift times but not be subtracted. I figure so I’ll need to use some kind of switch to tell it to not evaluate the comparison.
Does anyone have any thoughts how to go about this?
Thanks
