Home |
Search |
Today's Posts |
#3
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
I realise that once again I am answering my own posts but I figure people may
be able to either check my solution for glaring errors or maybe even use it in their own projects if it meets needs. I realised that two of the options had the same result so if I could seperate the third option and have that as the true condition then the false would be correct for both. The complete formula is as follows: IF(AND(((sum of travel finish - sum of travel start)+(sum of site finish - sum of site start))=6 hours,(site pm finish - site pm start)<=0),(site am finish - site am start)-30 minutes,IF(AND(((sum of travel finish - sum of travel start)+(sum of site finish - sum of site start))=6 hours,(sum of site pm finish - sum of site pm start)0),(sum of site am finish - sum of site am start)-15 minutes,(sum of site am finish- sum of site pm start))) It probably is a little hard to follow, but simply if 6 hours or more are worked then a break is recorded but only 15 minutes from this column if anything has been worked in the afternoon and 30 minutes if nothing has been worked in the afternoon. If under 6 hours has been worked nothing is deducted. I am a little proud of this one, I'm self taught and this feels like a MASSIVE step, although I imagine it is probably quite simple :o/ |