Help with if statements for date calculations

[MarioMac]

I have an excel spreadsheet where I need to calculate due dates daily. I have four categories, Overdue, Not Due, No Plan. I have an if statement for this and it works. =IF(ISBLANK(B2),"No Plan",IF(B2<TODAY(),"Overdue","Not due"))

My problem is that I also want to split the no plan into 2 categories. You only need a plan after 30 days of awareness. So I would like to have No Plan, and Plan not Required (if it's less than 30 days from Awareness date - column C).

[TABLE="width: 486"]
<colgroup><col span="2"><col></colgroup><tbody>[TR]
[TD][TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Status[/TD]
[TD]Due Date[/TD]
[TD]Awareness Date[/TD]
[/TR]
[TR]
[TD]Overdue[/TD]
[TD]3/31/2015[/TD]
[TD]3/19/2015[/TD]
[/TR]
[TR]
[TD]Not Due[/TD]
[TD]2/10/2016[/TD]
[TD]5/19/2015[/TD]
[/TR]
[TR]
[TD]No Plan[/TD]
[TD][/TD]
[TD]6/17/2015[/TD]
[/TR]
[TR]
[TD]No Plan[/TD]
[TD][/TD]
[TD]1/25/2016[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]I feel like I might be asking for too much but any help will be appreciated.

Thanks[/TD]
[TD][/TD]
[TD="align: right"]1/25/2016[/TD]
[/TR]
</tbody>[/TABLE]

 

[etaf]

on the forum you have lost the full code - this is because of < > being seen as html - put a space before and after

=IF(ISBLANK(B2),"No Plan",IF(B2<today(),"overdue","not due"))


=IF(AND ( ISBLANK(B2), C2 > today() - 30 ), "Plan not Required" IF ( ISBLANK(B2) , "No Plan", IF ( B2<today(),"overdue","not due"))
</today(),"overdue","not></today(),"overdue","not>

 

[MarioMac]

Thanks - I didn't see that it cut off my formula. This is the missing piece (I cannot post the whole one - it keeps cutting it off):
<today(),"overdue","not due"))
IF ( C2 < TODAY () , "Overdue" , "Not due" ) )

It doesn't work if I combine with yours.

<today(),"overdue","not due"))]<="" html=""></today(),"overdue","not></today(),"overdue","not>

 

[MarioMac]

I'm not winning this battle. The last part states that if the cell is more than today it's Overdue, if not it's Not due.

 

[etaf]

does this work
https://www.dropbox.com/s/7w4yoepwsktj02w/CondFmt_WkNo_Etaf.xlsx?dl=0

 

[MarioMac]

Can you view my data here:
https://www.dropbox.com/s/97vgfken0trav2o/test data.xlsx?dl=0

I would prefer not to use conditional formatting because I need to pivot table data afterwards.

 

[etaf]

ignore my post wrong reply !!!

 

[etaf]

Try

=IF(AND(ISBLANK(B2),C2 > TODAY() - 30),"No Plan Needed",IF(ISBLANK(B2),"No Plan",IF(B2 < TODAY(),"Overdue","Not due")))

https://www.dropbox.com/s/ma6ompy3vqy1r6m/test data_etaf.xlsx?dl=0

 

[MarioMac]

Thanks so much - works perfectly.

Try

=IF(AND(ISBLANK(B2),C2 > TODAY() - 30),"No Plan Needed",IF(ISBLANK(B2),"No Plan",IF(B2 < TODAY(),"Overdue","Not due")))

https://www.dropbox.com/s/ma6ompy3vqy1r6m/test data_etaf.xlsx?dl=0

 

[etaf]

your welcome