Let's resurrect this old post shall we?
It's been brought to my attention that this formula doesn't work correctly - it has a bug....
To recap - I needed a formula to work out overtime between some specific hours - this is for an overtime sheet - we can claim an hours before 07:30 and after 18:30.
We had this formula:
Quote:
|
=IF(ISBLANK(E10),"None",IF((TIMEVALUE("7:30 AM")-E10)<0,0,TIMEVALUE("7:30 AM")-E10)+IF((F10-TIMEVALUE("18:30"))<0,0,F10-TIMEVALUE("18:30")))
|
The problem? If you do over in an evening - say 19:00 to 20:00 - that should work out at one hour. The issue is that this formula calculates it as 1.5 hours - I think it's taking the extra half hour from 18:30.
How how can this formula be re-written to remove that bug?