Formula for date calculation based on certain criteria

[nikhil0311]

Excel 2007
	A	B	C	D	E	F	G
1			Reporting Dt --->	3/15/2016
2	Deal Stage	Deal Stage Dt	Created Dt	Final Tagging		Deal Stage	Logic (refer column A)
3	Lost			-		Lost	If Column A is "Lost" then Column D should be "-"
4	Lost/Cancelled	2/25/2014		-		Lost/Cancelled	If Column A is "Lost/Cancelled" then Column D should be "-"
5	Dead			-		Dead	If Column A is "Dead" then Column D should be "-"
6	Closed			-		Closed	If Column A is "Closed" then Column D should be "-"
7	Cancelled	3/10/2016	2/16/2015	-		Cancelled	If Column A is "Cancelled" then Column D should be "-"
8	Mandate	9/19/2014		(543)		Mandate	Column D = Deal stage Dt - Reporting dt (Column B - Column D1). If Column B is blank then Column D should be "-"
9	Bid		9/30/2015	(167)		Bid	Column D = Created Dt - Reporting dt (Column C - Column D1). If Column C is blank then Column D should be "-"
10	Non-Qualified opportunity	5/6/2015	5/6/2015	(314)		Non-Qualified opportunity	Column D = Created Dt - Reporting dt (Column C - Column D1). If Column C is blank then Column D should be "-"
11	Opportunity			-		Opportunity	Column D = Created Dt - Reporting dt (Column C - Column D1). If Column C is blank then Column D should be "-"
Sheet2

based on deal stage i.e. column A I want to do the final tagging in Column D. refer column F & G for the Logic

 

[MarcelBeug]

Maybe

=IF(AND(A3="Mandate",ISNUMBER(B3)),B3-$D$1,IF(AND(ISNUMBER(MATCH(A3,{"Bid","Non-Qualified opportunity","Opportunity"},0)),ISNUMBER(C3)),C3-$D$1,"-"))

 

[nikhil0311]

Hi Marcel. Thanks for the help. It means a lot. The formula is working fine for me. Hope you have a good day

 

[MarcelBeug]

You're welcome.