Hi
Cells are formatted as 00\:00 to allow me to input time without having to enter a colon.
On a time sheet an employee must have an 11 hour break between finish time and next day start time if this is broken they are compensated in 1/2 hourly payments
I'm using the Ceiling function to round to the nearest half hour, but on some occasions when there is an 11 hour break the result is still 30min penalty when it should be zero
Cell G204 is finish time on Monday (20:00), cell F205 is start time on Tuesday (07:00)
Formula is =CEILING((IF(AND($B$2-(TEXT(F204,"00\:00")-TEXT(G203,"00\:00")+(G203>F204))>0,H204>0,G203>0),$B$2-(TEXT(F204,"00\:00")-TEXT(G203,"00\:00")+(G203>F204)),0)),0.5/24)
Result is 00:30 (cell formatted hh:mm) but it should be 00:00
Also the anomaly only arises for 20:00 and 07:00, if finish time is 19:00 and start time is 06:00 the problem does not occur.
I've tried to make this as simple as possible, but I accept in advance that I may have failed here.
Thanks
Cells are formatted as 00\:00 to allow me to input time without having to enter a colon.
On a time sheet an employee must have an 11 hour break between finish time and next day start time if this is broken they are compensated in 1/2 hourly payments
I'm using the Ceiling function to round to the nearest half hour, but on some occasions when there is an 11 hour break the result is still 30min penalty when it should be zero
Cell G204 is finish time on Monday (20:00), cell F205 is start time on Tuesday (07:00)
Formula is =CEILING((IF(AND($B$2-(TEXT(F204,"00\:00")-TEXT(G203,"00\:00")+(G203>F204))>0,H204>0,G203>0),$B$2-(TEXT(F204,"00\:00")-TEXT(G203,"00\:00")+(G203>F204)),0)),0.5/24)
Result is 00:30 (cell formatted hh:mm) but it should be 00:00
Also the anomaly only arises for 20:00 and 07:00, if finish time is 19:00 and start time is 06:00 the problem does not occur.
I've tried to make this as simple as possible, but I accept in advance that I may have failed here.
Thanks