In addition, I'd replace your 0.3333333333333333 with 1/3, and your
0.208333333333333 with 1/48...
--
John C


"Barb Reinhardt" wrote:

I'd replace

SUM(F5-E5,F6-E6,F7-E7,F8-E8,F9-E9,F10-E10)
With
SUM(F5:F10)-SUM(E5:E10)
--
HTH,
Barb Reinhardt

If this post was helpful to you, please 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))))