IF, OR FUNCTION with dates

[temiomadeli]

=IF(OR(C111="YES",D111=""),"YES","")

Hi all

i am trying to create a formula that will return the word "YES" if criteria is met in cells C111 and D111

C111 either has YES or NO filled in the cells, while D111 has different dates, with some cells blank.

I am struggling with cell D111 as i dont know what value to put inside the "" as there are different dates from cell D111 downwards and i need a formula that will allow all the other cells to change and follow suit when you click on the drag down icon, it may be a case of changing the formula around. However as an excel this is not apparent.

please can someone help as i have been trying to solve this for ages.

 

[DebugGalpin]

=IF(OR(C111="YES",isdatevalid(D111)=True),"YES","")

or if d111 is a specific date you'll need to enter the number of the date e.g. 15/09/15 is 42557 so the formula would be

=IF(OR(C111="YES",D111=42557),"YES","")

or if you want d111 to be blank

=IF(OR(C111="YES",isblank(d111)),"YES","")

 

[temiomadeli]

=IF(OR(C111="YES",isdatevalid(D111)=True),"YES","")

or if d111 is a specific date you'll need to enter the number of the date e.g. 15/09/15 is 42557 so the formula would be

=IF(OR(C111="YES",D111=42557),"YES","")

or if you want d111 to be blank

=IF(OR(C111="YES",isblank(d111)),"YES","")

This doesn't appear to be working. column D111 and below will either have dates or a blank cell.

Heres the question am trying to answer if it helps.

Enter a formula that will return YES If they are on mat leave or have left the company?

COLUMN C111 is the column for mat leave and D111 the column for left company.

 

[Aladin Akyurek]

This doesn't appear to be working. column D111 and below will either have dates or a blank cell.

Heres the question am trying to answer if it helps.

Enter a formula that will return YES If they are on mat leave or have left the company?

COLUMN C111 is the column for mat leave and D111 the column for left company.

It is not clear when you want YES to obtain...

Is it?

when C111 = yes
and
when D111 is a date

 

[RCBricker]

=if(or(c111="Yes",d111<>""),"Yes","")

 

[temiomadeli]

yes that is correct

 

[DebugGalpin]

For mat leave.......if(c111="Yes","Yes","")
For left company I presume a blank is they have not left the company.... if(trim(D111)<>"","yes","")

combining the two gives....

if(or(c111="yes",d111<>""),"Yes","")

 

[temiomadeli]

Thank you this worked well. really appreciate your help. will definitely recommend this website.

 

[Aladin Akyurek]

It is not clear when you want YES to obtain...

Is it?

when C111 = yes
and
when D111 is a date

yes that is correct

Assuming that you are addressing my question...

=IF(AND(C111="yes",ISNUMBER(D111)),"yes","")