If Statement - Multiple Criteria

[spyldbrat]

I have 3 columns: Start Time, Pro Clock In, Ram Clock In. I am trying to find out the # of actual minutes there are between the two clock in's. However, there are few exceptions to the calculating the minutes.

Column H (Start Time)
Column J (Pro Clock In)
Column Q (Ram Clock in)

if Q = J, then the result would be nothing (or blank)
if J is earlier than Q, then the result would be nothing
if Q is earlier than H, then I want to subtract J from H BUT if J is earlier than J return the result of nothing
if Q is later than H, I want to subtract Q, then subtract Q from J

Thanks.

 

[Joe4]

Error in this statement:
if Q is earlier than H, then I want to subtract J from H BUT if J is earlier than J return the result of nothing

Can you clarify that?

 

[spyldbrat]

Sorry:

if Q is earlier than H, then I want to subtract J from H BUT if J is earlier than H return the result of nothing

 

[richh]

I'll try to figure something out, but the last statement is a bit hard. you expect two end results, so I'm not sure if what I come up with will work. Code below color coded for better codiness.

if Q = J, then the result would be nothing (or blank)
if J is earlier than Q, then the result would be nothing
if Q is earlier than H, then I want to subtract J from H BUT if J is earlier than J return the result of nothing - Please correct. code below assumes first J should be H
if Q is later than H, I want to subtract Q, then subtract Q from J - Please correct. Substract Q from H?

Not sure where you want the values to be displayed. I'm guessing you want the value to spit out in a single cell. Otherwise, it seems like you want to do a bunch of circular references and that'd be bad, mkay?

=IF(OR(Q9 = J9, J9 < Q9), "", If(Q9<H9, If(H9<J9, "", H9-J9), if(Q9>H9, (H9-Q9)+(J9-Q9), "I Screwed Something Up")))

 

[spyldbrat]

@richh, the last statement I wrote is correct, subtract Q from J

 

[Joe4]

Does this do what you are looking for?

=IF(OR(J7<=Q7,J7<H7),"",IF(Q7<H7,H7-J7,J7-Q7))

Note that you did not indicate what should happen if column Q equals column H.

 

[richh]

@richh, the last statement I wrote is correct, subtract Q from J

You want to subtract Q [From what], then Q from J

 

[spyldbrat]

Oh, I see what you're saying...(It's been a long day!!! Sorry!)

if Q is later than H, I want to subtract Q from J

 

[richh]

Oh, I see what you're saying...(It's been a long day!!! Sorry!)

if Q is later than H, I want to subtract Q from J

Gotcha!

=IF(OR(Q9 = J9, J9 < Q9), "", If(Q9<H9, If(H9<J9, "", H9-J9), if(Q9>H9, J9-Q9, "I Screwed Something Up")))

 

[spyldbrat]

Works perfectly!!!! Thank you so much!