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:

Unique Identifier	First Name	Last Name	Grade	Status	Completed On	+ Years			Due
12345	Jane	Doe	5	Not Applicable		1
12346	John	Doe	5	Not Applicable	13/12/2017	1			13/12/2018
12347	Jane	Smith	7	Not Applicable	12/11/2017	1			23/11/2018
12348	John	Smith	4	Due		1
12349	Jane	Jones	5	Not Applicable	11/12/2017	1			11/12/2018
12334	John	Jones	4	Completed	14/12/2017	1			14/12/2018
12335	Jane	Doe	6	Not Applicable	02/01/2018	1			02/01/2019
12336	John	Doe	5	Not Applicable	29/09/2017	1			27/09/2018
12337	Jane	Smith	4	Completed	27/09/2017	1			27/09/2018
12338	John	Smith	3	Due		1

<tbody>
</tbody>

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