Ceiling function for rounding hours anomaly ??

[August]

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

 

[jmacleary]

Hi there. Without trying to wrap my brain around the formula itself, what is happening is due to the way excel handles numbers (all date/times are stored internally as numbers). However, times of day are repsented as fractions of a day - excel has a limit to its precision and so depending on the values being represented, very slight inaccuracies can creep in. e.g. the time 22:00 is represented as 0.916666666666667 of a day - see the below table, so your addition/subtractions may result in a tiny inaccuracy. Add or subtract a very small value (e.g. 0.000001) at the relevant point(s) and the anomaly should disappear.

Book1
	J	K
203	6:00	0.25
204	19:00	0.791666667
205
206	7:00	0.291666667
207	22:00	0.916666667
Sheet1

​

 

[August]

Thank you, that makes sense
I can't have the times entered other than exact (in appearance at least), so I'll have to give it some more thought (never easy !!)

Thanks again for taking the time to reply

 

[jmacleary]

Hi there. You can make the adjustment in the formula rather where the times are entered - I think this MAY do it:

=CEILING((IF(AND($B$2-(TEXT(F204,"00\:00")-TEXT(G203,"00\:00")+(G203>F204))>0,H204>0,G203>0)-0.000001,$B$2-(TEXT(F204,"00\:00")-TEXT(G203,"00\:00")+(G203>F204)),0)),0.5/24)

 

[August]

I'll try that
Thanks again

 

[August]

I added the value in the 2nd half of the equation (after the if result) and it works

Thank you very much, I would never have thought of it

 

[jmacleary]

You're welcome and thanks for the feedback.