Is there a formula...........

[Guest]

Ok. Lets say there is a group of five cells ( A1 thru A5 ). Those cells are
formatted to show time ( h:mm ). I want to program cell A6 to show any time
over 8 hours that occurs in each individual cell as well as the sum of the
time over 8 hours of the group. However if the sum of the time of the group
in total is over 40 hours then I want cell A6 to show zero.
Example: A1= 8:00, A2= 9:00, A3= 9:00, A4=blank, A5=blank, I want A6= 2:00
But if A1= 8:00, A2= 9:00, A3= 9:00, A4=8:00, A5= 8:00, I want A6= zero

 

[Bernard Liengme]

=SUMPRODUCT(--(A1:A5>8/24),A1:A5-8/24)*(SUM(A1:A5)*24<40)
remember to format the cell with custom format [hh]:mm
best wishes

 

[Guest]

This formula is not doing what I want. It is doing this.

Example: A1= 08:00, A2= 09:00, A3= 09:00, A4=blank, A5=blank, A6 is = 24:00
I want A6 to = 02:00
Any other suggestions?
Lee D.

=SUMPRODUCT(--(A1:A5>8/24),A1:A5-8/24)*(SUM(A1:A5)*24<40)
remember to format the cell with custom format [hh]:mm
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

Ok. Lets say there is a group of five cells ( A1 thru A5 ). Those cells
are
formatted to show time ( h:mm ). I want to program cell A6 to show any
time
over 8 hours that occurs in each individual cell as well as the sum of the
time over 8 hours of the group. However if the sum of the time of the
group
in total is over 40 hours then I want cell A6 to show zero.
Example: A1= 8:00, A2= 9:00, A3= 9:00, A4=blank, A5=blank, I want A6= 2:00
But if A1= 8:00, A2= 9:00, A3= 9:00, A4=8:00, A5= 8:00, I want A6=
zero

 

[David Biddulph]

It gives 02:00 for me. I suggest that you check your formula and your data.

If in doubt, break the formula down, & check each element:

=(A1>8/24)
=A1-8/24
and so on for A2 to A5

=SUM(A1:A5)
--
David Biddulph

This formula is not doing what I want. It is doing this.

Example: A1= 08:00, A2= 09:00, A3= 09:00, A4=blank, A5=blank, A6 is =
24:00
I want A6 to = 02:00
Any other suggestions?

=SUMPRODUCT(--(A1:A5>8/24),A1:A5-8/24)*(SUM(A1:A5)*24<40)
remember to format the cell with custom format [hh]:mm
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

Ok. Lets say there is a group of five cells ( A1 thru A5 ). Those cells
are
formatted to show time ( h:mm ). I want to program cell A6 to show any
time
over 8 hours that occurs in each individual cell as well as the sum of
the
time over 8 hours of the group. However if the sum of the time of the
group
in total is over 40 hours then I want cell A6 to show zero.
Example: A1= 8:00, A2= 9:00, A3= 9:00, A4=blank, A5=blank, I want A6=
2:00
But if A1= 8:00, A2= 9:00, A3= 9:00, A4=8:00, A5= 8:00, I want
A6=
zero

 

[Guest]

Mr. Lienqme,
The formula you provided to me was very helpful. However if I place a word
in one of before mentioned cells, the cell containing the formula shows
######. Is there a way the formula would only recognize the time entries and
ignore a word entry?