An easier way?

P

Pyrite

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,"",((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))))

B

Barb Reinhardt

I'd replace

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

J

John C

0.208333333333333 with 1/48...
--
John C

Barb Reinhardt said:
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

Pyrite said:
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,"",((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))))

D

Don Guillett

This may help to shorten
=SUMPRODUCT((F5:F10)+(-E5:E10))

P

Pyrite

Thanks so much,

I have replaced the long decimals, I dont think I can replace the SUM
functions though because each one is doing a minus if you will, would this
produce the same result. They are not all being added together, for instance,
A2 - A1 is travel finish time - travel start time to leave it with amount of
time spent travelling. It can then add up all the hours spent travelling.

John C said:
0.208333333333333 with 1/48...
--
John C

Barb Reinhardt said:
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

Pyrite said:
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,"",((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))))

J

John C

Actually, Barb and Don are also correct. I'll give you an example.
Effectively, you are doing the following:
=SUM(8-5,10-3,4-2,8-4) etc.
which equates to 3+7+2+4 = 16
well, Don and Barb are saying
=SUM(8+10+4+8)-SUM(5+3+2+4) which equals 30-14 = 16
correct, it doesn't matter what order.
--
John C

Pyrite said:
Thanks so much,

I have replaced the long decimals, I dont think I can replace the SUM
functions though because each one is doing a minus if you will, would this
produce the same result. They are not all being added together, for instance,
A2 - A1 is travel finish time - travel start time to leave it with amount of
time spent travelling. It can then add up all the hours spent travelling.

John C said:
0.208333333333333 with 1/48...
--
John C

Barb Reinhardt said:
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

:

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,"",((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))))

T

Thomas [PBD]

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)))

T

Thomas [PBD]

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.

Thomas said:
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.

Pyrite said:
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,"",((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))))

P

Pyrite

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 said:
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.

Thomas said:
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.

Pyrite said:
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,"",((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))))

T

Thomas [PBD]

Pyrite,

You're very welcome.

--
--Thomas [PBD]
Working hard to make working easy.

Pyrite said:
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 said:
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.

Thomas said:
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.

:

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,"",((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))))