Simplify If Formula

[Poolshark25]

Hi

I have this formula that i have managed to get to return the results i need, but it is a it long-winded.

Is there a way this could be simplified?

=IF(C9=(TODAY()-7),"Retrain",IF(C9=(TODAY()-6),"Retrain",IF(C9=(TODAY()-5),"Retrain",IF(C9=(TODAY()-4),"Retrain",IF(C9=(TODAY()-3),"Retrain",IF(C9=(TODAY()-2),"Retrain",IF(C9=(TODAY()-1),"Retrain",IF(C9=(TODAY()),"Retrain",IF(C9<'Today -7'!$B$1,"Overdue","Ok")))))))))

I want Excel to return "Retrain" if an expiry date is within 7 days of todays date but if it has passed todays date to return "Overdue". If the expiry date is more than 7 days prior to todays date (Still valid) to return Ok"

Any help would be appreciated.

Regards,

Ernie

 

[Fluff]

How about
<today()-7,"overdue",if(c9></today()-7,"overdue",if(c9>=IF(C9< TODAY()-7,"Overdue",IF(C9 >TODAY(),"OK","Retrain"))

 

[Special-K99]

=IF(AND(C9>=TODAY()-7,C9<=TODAY()),"Retrain",IF(C9<'Today -7'!$B$1,"Overdue","Ok"))

 

[Special-K99]

How about
<today()-7,"overdue",if(c9></today()-7,"overdue",if(c9>=IF(C9< TODAY()-7,"Overdue",IF(C9 >TODAY(),"OK","Retrain"))

@Fluff: OP has 'Today -7'!$B$1 in the formula but its not in your solution?

 

[Fluff]

Good point, I missed that as was going more by the description than the formula.

 

[Gerald Higgins]

@Fluff: OP has 'Today -7'!$B$1 in the formula but its not in your solution?

My guess is that the reference to a sheet called 'Today-7', and cell $B$1 on that sheet, is a typo.

 

[Fluff]

Looks like we may never know.