试图将加班时间和常规时间分为3个单元格。早上加班，正常时间以及在Excel中加班。我从站点到站点，每个工作顺序都有自己的行。我正在从每行的开始时间中减去最终时间。如果有时间遍历上午08:00，则为06：00-10：00。然后，我需要一个单元格计算才能说2个小时的加时性和下一个单元格计算，以说2个小时的常规薪酬。对于第三个单元的情况，除了定期薪水在下午5:00，即4:00 pm至8:00 pm，1小时的正常付款和3小时的加班。
我以为我有= sumproduct，但计算机崩溃了。已经三个星期了，我无法接近解决方案。另外，我需要定期的薪水计算才能在8:00 am至5:00 pm之间进行计算，无论时间计算如何，有时我工作16小时
trying to separate overtime and regular time into 3 cells. morning overtime, regular time, and after hours overtime in Excel. I go from site to site, and each work order gets its own row. I am subtracting end time from start time on each row. If a time goes through 08:00am, i.e. 06:00 - 10:00. then I need one cell calculation to say 2 hours overtime and the next cell calculation to say 2 hours of regular pay. same thing for the third cell except regular pay end at 5:00pm, i.e. 4:00pm to 8:00pm, 1 hours of regular pay and 3 hours of overtime.
I thought that I had it with =SUMPRODUCT, but computer crashed. been three weeks and I cannot get near to a solution. Also I need the the regular pay calculation to calculate only between 8:00am and 5:00pm no matter what is in the time calculation, sometimes I work 16 hours days
发布评论
评论（1）
这是一个可能的解决方案（用Office365进行了测试）：
第2行的示例：
Here's a possible solution (tested with Office365) :
Example for line 2 :