Simplify If Formula

Poolshark25

New Member
Joined
Jan 3, 2019
Messages
26
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
 

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
38,760
Office Version
365
Platform
Windows
How about
<today()-7,"overdue",if(c9></today()-7,"overdue",if(c9>=IF(C9< TODAY()-7,"Overdue",IF(C9 >TODAY(),"OK","Retrain"))
 
Last edited:

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,341
=IF(AND(C9>=TODAY()-7,C9<=TODAY()),"Retrain",IF(C9<'Today -7'!$B$1,"Overdue","Ok"))
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
38,760
Office Version
365
Platform
Windows
Good point, I missed that as was going more by the description than the formula.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
38,760
Office Version
365
Platform
Windows
Looks like we may never know.
 

Watch MrExcel Video

Forum statistics

Threads
1,095,746
Messages
5,446,253
Members
405,392
Latest member
Steveoaktree1977

This Week's Hot Topics

Top