Thomas, thanks a lot for that, it is an excellent suggestion. I am going to
try and implement now because since the last post I have had to add another
condition, the 30 minute lunch break (-1/48) should not be taken until 6
hours have been worked. I have added it but put the IF in the wrong place and
it only counts site time toward this six hours when it should count travel
aswell. Your way would make it much easier to review the brackets etc.

"Thomas [PBD]" wrote:

Even further variable method:
A1: 0.020833333333333
A2: 0.333333333333333
A3: SUM(E5:E10)
A4: SUM(F5:F10)
A5: SUM(G5:G10)
A6: SUM(H5:H10)

=IF(((A4-A3)+IF((A6-A5-A1)<=A2,(A6-A5-A1),A2))<0,"",((A4-A3)+IF((A6-A5-A1)<=A2,(A6-A5-A1),A2)))

--
--Thomas [PBD]
Working hard to make working easy.
Answered your question? Click ''''Yes'''' below.


"Thomas [PBD]" wrote:

Pyrite,

I have consolidated your formula a little. This might be easier to read if
you were to use variables instead of whole characters. I will show an
example lower.

Consolidated:
=IF(((SUM(F5:F10)-SUM(E5:E10))+IF((SUM(H5:H10)-SUM(G5:G10)-0.0208333333333333)<=0.333333333333333,(SUM(H5:H10 )-SUM(G5:G10)-0.0208333333333333),0.333333333333333))<0,"",((SUM (F5:F10)-SUM(E5:E10))+IF((SUM(H5:H10)-SUM(G5:G10)-0.0208333333333333)<=0.333333333333333,(SUM(H5:H10 )-SUM(G5:G10)-0.0208333333333333),0.333333333333333)))

Variables method:
A1: 0.020833333333333
A2: 0.333333333333333

=IF(((SUM(F5:F10)-SUM(E5:E10))+IF((SUM(H5:H10)-SUM(G5:G10)-A1)<=A2,(SUM(H5:H10)-SUM(G5:G10)-A1),A2))<0,"",((SUM(F5:F10)-SUM(E5:E10))+IF((SUM(H5:H10)-SUM(G5:G10)-A1)<=A2,(SUM(H5:H10)-SUM(G5:G10)-A1),A2)))

--
--Thomas [PBD]
Working hard to make working easy.
Answered your question? Click ''''Yes'''' below.


"Pyrite" wrote:

Hi,

I have already posted a couple of things today with regards to this and I
have finally found my own solution. The only thing is that the formula seems
a little monstrous to say the least. Basically this formula totals up time
travelled and adds it to time on site so long as time on site is not more
than 8 hours. If it is more that 8 hours then the formula simply adds the 8
to the travel time instead with the left over dealt with elsewehere. The next
thing it does is display nothing if the answer is less than 0, the reason for
this is that the format of the cell containing this formula is [h]:mm so if
the date is negative then it just displays ######## which means at the bottom
of the sheet the sum total of time worked will not add up properly. Thats it
really it adds time travelled and time worked (so long as no more than 8
hours on site) if more than 8 hours it just adds 8 and if the number is
negative it displays nothing.

What do you think? Is there a clearer way than this as it is quite confusing.

=IF(((SUM(F5-E5,F6-E6,F7-E7,F8-E8,F9-E9,F10-E10))+(IF((SUM(H5-G5,H6-G6,H7-G7,H8-G8,H9-G9,H10-G10)-0.0208333333333333)<=0.333333333333333,((SUM(H5-G5,H6-G6,H7-G7,H8-G8,H9-G9,H10-G10)-0.0208333333333333)),0.333333333333333)))<0,"",((S UM(F5-E5,F6-E6,F7-E7,F8-E8,F9-E9,F10-E10))+(IF((SUM(H5-G5,H6-G6,H7-G7,H8-G8,H9-G9,H10-G10)-0.0208333333333333)<=0.333333333333333,((SUM(H5-G5,H6-G6,H7-G7,H8-G8,H9-G9,H10-G10)-0.0208333333333333)),0.333333333333333))))