If statement with 4 conditions.

[sdoppke]

Hi everyone, I am hoping to get help on what seems to be a complicated IF statement (to ME )

In words it looks like this:
If A1(6:00AM) is <than C1(11:00AM) then = "O" OR if A2(9:00PM) is >than C2(11:00PM) then = "C" OR If A1 and A2 are in between C1 and C2 then "M" or is none of these then "" (blank).

thank everyone in advance, hope that was clear as mud

sd

 

[Joe4]

I will lay out each value
Currently:
D13=blank
F13=blank
C10=11:00AM
F10=6:00PM

I was hoping for a return of blank
I have tried with different calues in D13 and F13 still same error

Does it make a difference that all four cell have formulas in them?

Yes it makes a difference. A blank cell is treated like zero, so D13 would be considered to be less than C10 (0 is less than 11:00 AM).

This formula could get a bit more complex if you need to account for null/blank values and handle them differently. Can you explain what is in each of these four cells, and maybe just lay out all your conditions in plain English, in the hierarchy they need to be (including how to handle blank values)?

 

[Joe4]

You also may find these two detailed write-ups on Nested IF functions in Excel helpful:
http://personal-computer-tutor.com/if2.htm
http://www.techonthenet.com/excel/formulas/if_nested.php

 

[sdoppke]

Yes it makes a difference. A blank cell is treated like zero, so D13 would be considered to be less than C10 (0 is less than 11:00 AM).

This formula could get a bit more complex if you need to account for null/blank values and handle them differently. Can you explain what is in each of these four cells, and maybe just lay out all your conditions in plain English, in the hierarchy they need to be (including how to handle blank values)?

You bet:
D13:=VLOOKUP(A13,'ST(A)'!A2:O29,2,0) Looks up the scheduled start time
F13:=VLOOKUP(A13,'ST(A)'!A2:O29,3,0) Looks up the scheduled end time
C10:=VLOOKUP(MyStoreInfo!$C$2,StoreHours!$C$2:$S$2500,4) Looks up store open hour
F10:=VLOOKUP(MyStoreInfo!$C$2,StoreHours!$C$2:$S$2500,5) Looks up store close hour

In a paragraph: If the person is set up to be there before open, they will get an "O" for opener
If the person is set up to be there after close, they will get a "C" for closer
If they are set up to be there between store operating hours, they will receive a "M" for Midshift
If the vlookup returns not data (blank) they will get NO letters and no conditional formating happens

Thanks again for taking the time to help!

sd

 

[Joe4]

Thanks. That makes a bit more sense to me now.

See if this works for you. I only bothered checking if the Start Time was blank, figuring either the Start and End times would BOTH be populated or BOTH be blank, but not one populated and one blank.

=IF(D13=0,"",IF(D13 < C10,"O",IF(F13 > F10,"C","M")))

 

[sdoppke]

Thanks. That makes a bit more sense to me now.

See if this works for you. I only bothered checking if the Start Time was blank, figuring either the Start and End times would BOTH be populated or BOTH be blank, but not one populated and one blank.

=IF(D13=0,"",IF(D13 < C10,"O",IF(F13 > F10,"C","M")))

That worked perfect, I just had to change the 0 to a ""

=IF(D13="","",IF(D13 < C10,"O",IF(F13 > F10,"C","M")))

thanks for hangin in there with me!!

sd

 

[Joe4]

Your welcome! Glad we got it all sorted out!