formula inflexibility

[Guest]

The following equation does what most of what I want. All values are times with the format h:mm.

=IF(OR(C2="YES",C2=""),IF(AND(E2>=D2,G2>=F2,I2>=H2),(E2-D2)+(G2-F2)+(I2-H2),"Time missing or mistyped"),"OFF").

An example of what it doesn't do is allow me to see a total for rows where G2<F2 and/or I2<H2 when E2>=D2.
I'd like to make it flexible enough to SUM only the combinations that return a positive value while ignoring the others. This would allow me to have a running total instead of the text message "Time missing or mistyped."
Thanks,

tj

 

[Frank Kabel]

Hi
SUM
would ignore text entries. But if you can also have negative entries
use
=SUMIF(B1:B100,">0")

 

[Guest]

Frank,

I don't think I explained what I was going for very well. On the bright side, your post did let me brainstorm what I needed. SUMIF won't do what I wanted--to subtract three sets of times only if the second time is larger than the first (since this is a time sheet, it simply signifies that the second time has been entered accurately). Once the three time sets have been evaluated, I wanted them added together. If it was a Day Off, I wanted to return OFF.
This formula worked, although I still feel it's a bit unwieldly.

=IF((OR(C2="YES",C2="")),SUM(IF(E2>=D2,E2-D2,0),IF(G2>=F2,G2-F2,0),IF(I2>=H2,I2-H2,0)),"OFF")

thanks,

tj

 

[Frank Kabel]

Hi
could you please provide some sample data (plain text - no attachment
please) for your cells (C2, G2, etc.) and describe your expected resul

 

[Guest]

C2 D2 E2 F2 G2 H2 I2
YES 8:09 AM 12:53 PM 1:27 PM 5:41 PM BLANK BLANK

I want the formula, which is in K2, to SUM up the differeces between the times as follows:
(E2-D2)+(F2-G2)+(I2-H2)
The problem I was having occurred when the first number in one of the pairs had been entered but not the second number.
For instance:
D2 E2
8:09 AM Blank

Various formulas I tried either returned an error or an incorrect value.

In order for the running total to be accurate, I needed the formula to ignore such a blank and ignore typos that made the second date smaller than the first. Example, if E2 was 7:00 AM when D2 was 8:09 AM.

I'm now using this formula, and it works...I just hoped an easier version I overlooked might be possible.

=IF((OR(C2="YES",C2="")),SUM(IF(E2>=D2,E2-D2,0),IF(G2>=F2,G2-F2,0),IF(I2>=H2,I2-H2,0)),"OFF")

The logical_test evaluates whether or not the given day is a working day, with C2 containing YES or NO.

tj

 

[Frank Kabel]

Hi
try
=IF(OR(C2="YES",C2=""),MAX(E2-D2,0)+MAX(F2-G2,0)+MAX(I2-H2,0),"OFF")

 

[Guest]

That is better. Thanks.

tj

Hi
try
=IF(OR(C2="YES",C2=""),MAX(E2-D2,0)+MAX(F2-G2,0)+MAX(I2-H2,0),"OFF")