Hi there!
I currently have a worksheet, that counts hours worked by the employees. The problem is, that each day that they work, they have a 1hr brake, which is not payed.
Each day is calculated using this formula:
=IF(ISBLANK(I2);"0,00";IF(ISBLANK(I4);"0,00";((((IF(I4<I2;I4+1;I4)-I2)-(MOD(IF(I2>I4;MIN(I4;TIME(7;0;0))-MAX(I2;TIME(22;0;0));MAX(0;TIME(7;0;0)-I2)+MAX(0;I4-TIME(22;0;0)));1)))*24)*A3)+((MOD(IF(I2>I4;MIN(I4;TIME(7;0;0))-MAX(I2;TIME(22;0;0));MAX(0;TIME(7;0;0)-I2)+MAX(0;I4-TIME(22;0;0)));1)*24)*(A3*1,5))))
The day rate is regular, the night rate is *1.5(the night rate starts at 22:00, ends at 7:00) I need that each time the start and end times are filled in, there would be a one hour deduction in total day, the deducted rate is counted in regular day rate.
Does anyone have any ideas how to achieve this?
I'm also attaching an example workbook, to better understand my needs.
Bookmarks