Lots of nested IF's after meeting certain criteria?

[wintye]

Hi All,

I hope you're enjoying your Sunday evening.

I have the following table that needs a formula inserting in the 'Status' column


Table:


[TABLE="class: cms_table, width: 0"]
<tbody>[TR]
[TD]Unique Identifier[/TD]
[TD]First Name[/TD]
[TD]Last Name[/TD]
[TD]Grade[/TD]
[TD]Status[/TD]
[TD]Completed On[/TD]
[TD]+ Years[/TD]
[TD][/TD]
[TD][/TD]
[TD]Due[/TD]
[/TR]
[TR]
[TD]12345[/TD]
[TD]Jane[/TD]
[TD]Doe[/TD]
[TD]5[/TD]
[TD]Not Applicable[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12346[/TD]
[TD]John[/TD]
[TD]Doe[/TD]
[TD]5[/TD]
[TD]Not Applicable[/TD]
[TD]13/12/2017[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD]13/12/2018[/TD]
[/TR]
[TR]
[TD]12347[/TD]
[TD]Jane[/TD]
[TD]Smith[/TD]
[TD]7[/TD]
[TD]Not Applicable[/TD]
[TD]12/11/2017[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD]23/11/2018[/TD]
[/TR]
[TR]
[TD]12348[/TD]
[TD]John[/TD]
[TD]Smith[/TD]
[TD]4[/TD]
[TD]Due[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12349[/TD]
[TD]Jane[/TD]
[TD]Jones[/TD]
[TD]5[/TD]
[TD]Not Applicable[/TD]
[TD]11/12/2017[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD]11/12/2018[/TD]
[/TR]
[TR]
[TD]12334[/TD]
[TD]John[/TD]
[TD]Jones[/TD]
[TD]4[/TD]
[TD]Completed[/TD]
[TD]14/12/2017[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD]14/12/2018[/TD]
[/TR]
[TR]
[TD]12335[/TD]
[TD]Jane[/TD]
[TD]Doe[/TD]
[TD]6[/TD]
[TD]Not Applicable[/TD]
[TD]02/01/2018[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD]02/01/2019[/TD]
[/TR]
[TR]
[TD]12336[/TD]
[TD]John[/TD]
[TD]Doe[/TD]
[TD]5[/TD]
[TD]Not Applicable[/TD]
[TD]29/09/2017[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD]27/09/2018[/TD]
[/TR]
[TR]
[TD]12337[/TD]
[TD]Jane[/TD]
[TD]Smith[/TD]
[TD]4[/TD]
[TD]Completed[/TD]
[TD]27/09/2017[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD]27/09/2018[/TD]
[/TR]
[TR]
[TD]12338[/TD]
[TD]John[/TD]
[TD]Smith[/TD]
[TD]3[/TD]
[TD]Due[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



I need the formula inserted in the Status column to return the values as shown above.


"Not Applicable" if the grade is 5 or higher. (Some that are grade 5 or above have completed the course but I need it to ignore that and still show as not available.

"Due" if a Grade 3 or 4 doesn't have a date entered in the 'completed on' column
"Due in 2 wks" if the due column is today's date + 14
"Due in 4 wks" if the due column is today's date + 28
"Expired" if less than Today's Date
"Completed" if the date is within a year (less a month)

Any help would be greatly greatly appreciated.

Let me know if you have any questions

Thank you in advance
Wintye

 

[Dryver14]

what does this line mean exactly

"Completed" if the date is within a year (less a month)

 

[wintye]

Hi Dryver14,

Thanks for your reply.

The Spreadsheet is to manage training. This course needs to expire before it can be completed again. It then needs to be completed within 1 month of the expiry date.

In order to allow me to send reminders, I also want it to show when they have 2 wks to complete, 4 wks to complete, when it's due to be completed (as should have but never taken), or when expired (4 weeks after the expiry date).

Additionally I need the cell to show as "Not Applicable" if the user isn't a grade 1, 2, 3, or 4.



Basically if the Unique Identifier relates to a grade 1, 2, 3, or 4 and there's a date entered in the 'Completed' column, then I need the 'Status' cell to show as "Completed as well as 2 wks etc.

Thanks for any help you can offer

Wintye