An easier way?

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

 

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

 

[John C]

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

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.

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

 

[Don Guillett]

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

 

[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.

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

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.

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

 

[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
When adding and subtracting, so long as the sign in front of each number is
correct, it doesn't matter what order.
--
John C

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.

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

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.



:

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

 

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

 

[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.
Answered your question? Click ''''Yes'''' below.

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.

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

 

[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.

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.

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.

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

 

[Thomas [PBD]]

Pyrite,

You're very welcome.

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

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.

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.

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.


:

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