Nested Formula Help

[kittymongo]

I need a nested formula to pull a specific code type in a column. My transaction code field needs to pull AD, FY or RN = (Advances, First Year or Renewals). If the Earned/Advanced column has Advance, this automatically equals AD. If the Earned/Advanced column has Earned, this needs to be FY or RN depending on the date. Is there a nested formula I can use to pull this in one try? I have a work around using multiple columns but would be easier as one.

If the Effective Date is 365 days or less and the Earned/Advanced is Earned, this needs to read FY. If the Effective Date is over 365 days and the Earned/Advanced is Earned, this needs to read RN.

[TABLE="width: 347"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Effective Date[/TD]
[TD]Earned/Advanced[/TD]
[TD]Trans Code[/TD]
[/TR]
[TR]
[TD]07/13/2018[/TD]
[TD]Advance[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]05/01/2016[/TD]
[TD]Advance[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]09/22/2017[/TD]
[TD]Earned[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10/31/2018[/TD]
[TD]Earned[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I hope I gave enough information here to figure this out? Please help.

 

[Fluff]

How about

Excel 2013 32 bit
	A	B	C
1	Effective Date	Earned/Advanced	Trans Code
2	13/07/2018	Advance	AD
3	01/05/2016	Advance	AD
4	10/08/2017	Earned	RN
5	31/10/2018	Earned	FY
Sheet2

Cell Formulas
Range	Formula
C2	=IF(B2="Advance","AD",IF(A2TODAY(),-12),"RN","FY"))

 

[kittymongo]

You are a life saver! Thank you so much and this worked perfectly.

 

[jtakw]

Hi,

If you literally mean 365 Days as the cut-of between FY and RN:

Book1
	A	B	C
1	Effective Date	Earned/Advanced	Trans Code
2	7/13/2018	Advance	AD
3	5/1/2016	Advance	AD
4	9/22/2017	Earned	FY
5	10/31/2018	Earned	FY
6	8/19/2017	Earned	RN
7	8/20/2017	Earned	FY
Sheet194

Cell Formulas
Range	Formula
C2	=IF(B2="Advance","AD",IF(A2>=TODAY()-365,"FY","RN"))

 

[kittymongo]

Thank you kindly! This is much appreciated. I did update my formula to the day range.

 

[Fluff]

Glad we could help & thanks for the feedback

 

[jtakw]

You're welcome, glad to help.

Just a brief explanation (FYI), you should use Fluff's formula if you want 12 months as cut off between FY and RN.

Use my version Only if you want 365 days. The difference is if current or previous year is a Leap Year, which has 366 days.