Help regarding a IF Function with multiple conditions...

ajayxlnc · Jul 30, 2013

Hello,

1)I have a column where in i have the status of a user like Approved, Committed, New and few other

2) There is a due date column where in date is mentioned in yyyy-mm-dd format

I want the below conditions to be satisfiedMy Requirement is :

**) I want an If condition where in it will check whether the status is Approved & Committed & New and i want to display a message "Date is Over" if the date mentioned in the column is crossing today's date (2013-07-31)

**) If condition where in it will check whether the status is Approved & Committed & New and i want to display a message "Target Release" if the date mentioned in the column is crossing today's date (2013-10-30)

Please help me out in finding a formula

Faseeh · Jul 30, 2013

Hello,

With your data in A1:B8, try this in C1 and drag down:

[pre]

Code:

Approved & Committed & New	5-Jul-13
Approved & Committed & New	16-Jul-13
Approved & Committed & New	30-Jul-13
Approved & Committed & New	13-Jul-13
Approved & Committed & New	23-Jul-13
Not Approved & Committed & New	22-Jul-13
Not Approved & Committed & New	25-Jul-13
Not Approved & Committed & New	27-Jul-13[/pre]
=IF(A1=&#34;Approved & Committed & New&#34;,IF(B1&#60;TODAY(),&#34;Date is Over&#34;,&#34;Target Date&#34;),&#34;-&#34;)

Regards,

ajayxlnc · Jul 30, 2013

Hello Faseeh,

Thanks, but there is a correction.

First the formula in E1 has to check whether in Cell C1 there is which status (Approved/Committed/New) If the Cell C1 Contains any of the three above mentioned status then it has to satisy the next conditions that is the date condition.

Please help me out

[pre]

Code:

C                 D               E
Approved	2013-07-17	Date Is Over
Committed	2013-07-17	Date Is Over
Approved	2013-07-17	Date Is Over
Approved	2013-07-17	Date Is Over
Committed	2013-07-17	Target Date
Approved	2013-07-17	Date Is Over
Approved	2013-07-31	Target Date
Approved	2013-07-31	Date Is Over
New	        2013-07-31	Target Date
Committed	2013-07-31	Date Is Over
Approved	2013-07-31	Date Is Over
Approved	2013-07-31	Date Is Over
Committed	2013-07-31	Date Is Over
Approved	2013-07-31	Date Is Over
New	        2013-07-31	Date Is Over

[/pre]
Edited with back ticks

Faseeh · Jul 30, 2013

Hi ajayxlnc,

With data in Col C to E, and when Date<Today mean Date is Over, try this in E1 and drag down:

Code:

=IF(ISERROR(MATCH(C1,{&#34;Approved&#34;,&#34;Committed&#34;,&#34;New&#34;},0))=TRUE,&#34;&#34;,IF(D1&#60;TODAY(),&#34;Date is Over&#34;,&#34;Target Date&#34;))

Regards,

ajayxlnc · Jul 31, 2013

Hello Faseeh,

There is a problem again.

For this date 2013-07-17 (Example) the formula is retreiving Target Date which is less than the 2013-07-31. Can you please Edit the formula and send it.

Instead of Today use the date 2013-07-31 (31st July) I have the dates column in this format 2013-07-17, due to this the formula is not working properly or what ?

Please help me out

Faseeh · Jul 31, 2013

Hi ajayxinc,

I don't think there is a proble, I got following output:

[pre]

Code:

Approved	7/17/2013	Date is Over
Committed	7/17/2013	Date is Over
Approved	7/17/2013	Date is Over
Approved	7/17/2013	Date is Over
Committed	7/17/2013	Date is Over
Approved	7/17/2013	Date is Over
Approved	7/31/2013	Target Date
Approved	7/31/2013	Target Date
New	7/31/2013	Target Date
Committed	7/31/2013	Target Date
Approved	7/31/2013	Target Date
Approved	7/31/2013	Target Date
Committed	7/31/2013	Target Date
Approved	7/31/2013	Target Date
New	7/31/2013	Target Date

[/pre]

Should i upload the sample file?

Regards,

ajayxlnc · Jul 31, 2013

Yeah can you please do that as i am getting the other way as i described, Thanks

Faseeh · Jul 31, 2013

Please see this one:

https://dl.dropboxusercontent.com/u/60644346/ajayxlnc_multiple%20if.xlsx

ajayxlnc · Aug 1, 2013

Faseeh,

Yes the file looks great and i have made few changes and i am able to get the required output.

I have one more request, one more nested if function to be added to the formula

=IF(ISERROR(MATCH(E2,{"Approved","Committed","New"},0))=TRUE,"",IF(J2<TODAY(),"Delivery Date already over","Target Release T2 - Delivered after 31.10.2013"))

For this there is one more addition there are two more status which have to be added.

If the E2 contains Approved, Committed, New and also the date which is less than today should be displayed as "Date Already Over" and rest "Target Release -----"

I want the following

If the status is Not Approved, Committed, New (Ex: Done, Removed) then it should show the Comment "Already Done".

Can we mix both the conditions in one formula.

Like if the cel E2 Contains Approved, Commmitted, New along with the date condition then display it will display "Date Already Over" and rest "Target Release -----"

and

If the cell E2 Not Contains Approved, Committed, New (Ex: Done, Removed) along with the date condition display "Already Done"

**

One More has to be added, for few cells in the date column Date is Blank so if the date is there then it will fulfil the above condition in the formula and if date is blank display "Date Missing"

Can you help me out in nesting these if function in the above formula.

Thanks, you have been really helpful.

Faseeh · Aug 1, 2013

Hi aljex,

Try replacing formula in sample file with this one:

Code:

=IF(ISERROR(MATCH(C6,{&#34;Approved&#34;,&#34;Committed&#34;,&#34;New&#34;},0))=TRUE,&#34;Already Done&#34;,IF(D6&#60;TODAY(),&#34;Date is Over&#34;,&#34;Target Date&#34;))

..and drag down.

Regards,

ajayxlnc · Aug 2, 2013

Thanks Faseeh,

I want the Already Done comment to be added in the nested if function after satisfying the date condition also

=IF(ISERROR(MATCH(E2,{"Approved","Committed","New"},0))=TRUE,"",IF(J2<TODAY(),"Delivery Date already over","Target Release T2 - Delivered after 31.10.2013"))

This formula is good but i want an addition to this formula

If E2 Contains Approved, Committed, New then the date condition which mentioned above satisfies and the above formula is very much working and fine

But i also have other status in Column E for which i want a nested if function added to the above formula and that too after the date condition is satisfied not before that one.

If E2 Contains Removed, Done then it would do the same thing in the formula (check for the date condition and also give the result "Already Over" but this has to be nested to the below formula

=IF(ISERROR(MATCH(E2,{"Approved","Committed","New"},0))=TRUE,"",IF(J2<TODAY(),"Delivery Date already over","Target Release T2 - Delivered after 31.10.2013"))

And one more addition to the date condition

**********

One More has to be added, for few cells in the date column Date is Blank so if the date is there then it will fulfil the above condition in the formula and if date is blank display "Date Missing" in the same formula

**********

Can we do this or is there any other way where in i can get this through IF function or can you help me out if there is a macro or any other alternative for solving my issue

Faseeh · Aug 2, 2013

Hi ajayxlnc,

You are always welcome! Can you upload a sample file with all the conditions and some sample data, might we work out a formula that is compactor and still satisfies all the conditions?

Regards,

ajayxlnc · Aug 2, 2013

Thanks Yaar Faseeh,

Check out the sample data

https://docs.google.com/file/d/0B_8nXiEldUKKZUVXV0EwRDZnTjQ/edit?usp=sharing

From B351:B1136 in Date column the dates are blank and there are no dates, but in the remarks columnD it is showing as Delivery Date already over (Ex: D430:D432). But for these dates are missing.

What i want is for the same formula......

=IF(ISERROR(MATCH(A412,{"Approved","Committed","New"},0))=TRUE,"",IF(B412<TODAY(),"Delivery Date already over","Target Release T2 - Delivered after 31.10.2013"))--> After this"data Not available"

.... If the dates in the ColumnB is blank then the comment should be Data not available but that condition has to be satisfied after all the conditions in the above formula are satisfied. you got it right...

Faseeh · Aug 3, 2013

Hi ajayxlnc,

please try this:

Code:

=IF(ISBLANK(B1135)=TRUE,&#34;Date Not Available&#34;,IF(ISERROR(MATCH(A1135,{&#34;Approved&#34;,&#34;Committed&#34;,&#34;New&#34;},0))=TRUE,&#34;&#34;,IF(B1135&#60;TODAY(),&#34;Delivery Date already over&#34;,&#34;Target Release T2 - Delivered after 31.10.2013&#34;)))

Hope that your problem is resolved!!

Regards,

Help regarding a IF Function with multiple conditions...

ajayxlnc

New Member

Faseeh

Excel Ninja

ajayxlnc

New Member

Faseeh

Excel Ninja

ajayxlnc

New Member

Faseeh

Excel Ninja

ajayxlnc

New Member

Faseeh

Excel Ninja

ajayxlnc

New Member

Faseeh

Excel Ninja

ajayxlnc

New Member

Faseeh

Excel Ninja

ajayxlnc

New Member

Faseeh

Excel Ninja