Formula with many conditions required please

[Orangeanorak]

Grateful if a formula can be supplied for this one please. No VBA if possible
Column E deadline
If D5 = Emergency and the date in A5 is equals today insert text "In Progress" if = 1 day more insert text "Expires Today" if more than 1 day insert text "Expired"
If D5 = Urgent and the date in A5 is less than 3 days ahead insert text "In Progress" if = 3 days ahead insert text "About to Expire" if 4 days ahead insert text "Expires Today" if more than 4 days insert text "Expired"
If D5 = Routine and the date in A5 is less than 24 days ahead insert text "In Progress" if 24 to 27 days ahead insert text "About to Expire" if 28 days ahead insert text "Expires Today" if more than 28 days insert text "Expired"

Thank you --------- Mission Impossible ?????

 

[Orangeanorak]

Ok, be sure to use the formula in Post # 18, post back if it needs further modification or if it works for you.

Hi this works on Emergency but not correctly on Urgent or Routine - I may have to find a simpler way of doing this with less criteria. My post #13
Also none of the Col A dates are ever in the future --- I use DD/MM/YYYY DATE format

 

[Fluff]

Hello again. I dont have any data to post.

Then can you please make some up showing exactly what the various outcomes should be.

 

[Orangeanorak]

Hi Guys -- this is what I wish to achieve for jobs entered on 19/01/2022 - I cant thank you enough for this

 

[Fluff]

Can you enter dates in col A & show the expected results based on today's date.

 

[Orangeanorak]

Can you enter dates in col A & show the expected results based on today's date.

Hi again --- Rows 5, 8 and 14 show todays date and expected results

 

[Fluff]

If you cannot be bothered to mock-up some data as requested, then I cannot help.

 

[Orangeanorak]

If you cannot be bothered to mock-up some data as requested, then I cannot help.

Sorry to annoy you Fluff. Its not a matter of not being bothered; its lack of knowledge on the subject. The rows 5, 8 and 14 indicated show the results I require for todays date - I am asking for the formula to make that work and also produce the other criteria shown. Obviously all this will shift as the date changes. Off for a cup of tea now. If you cant help I'll try a simpler version.

 

[jtakw]

This version of my formula is fully tested according to your requirements as I understand it, seems to be working as expected:

Book3.xlsx
	A	B	C	D	E
4	Date reported			Status	Deadline
5	1/19/2022			Emergency	Expires Tomorrow
6	1/18/2022			Emergency	Expires Today
7	1/17/2022			Emergency	Expired
8	1/19/2022			Urgent	In Progress
9	1/18/2022			Urgent	In Progress
10	1/17/2022			Urgent	In Progress
11	1/16/2022			Urgent	Expires Tomorrow
12	1/15/2022			Urgent	Expires Today
13	1/14/2022			Urgent	Expired
14	1/19/2022			Routine	In Progress
15	12/29/2021			Routine	In Progress
16	12/28/2021			Routine	About to Expire
17	12/24/2021			Routine	About to Expire
18	12/23/2021			Routine	Expires Tomorrow
19	12/22/2021			Routine	Expires Today
20	12/21/2021			Routine	Expired
Sheet943

Cell Formulas
Range	Formula
E5:E20	E5	=IFERROR(LOOKUP(A5-TODAY()+IF(D5="Emergency",1,IF(D5="Urgent",4,28)),IF(D5="Routine",{0,1,2,7},{0,1,2}),IF(D5="Routine",{"Expires Today","Expires Tomorrow","About to Expire","In Progress"},{"Expires Today","Expires Tomorrow","In Progress"})),"Expired")

 

[Orangeanorak]

This version of my formula is fully tested according to your requirements as I understand it, seems to be working as expected:

Book3.xlsx
	A	B	C	D	E
4	Date reported			Status	Deadline
5	1/19/2022			Emergency	Expires Tomorrow
6	1/18/2022			Emergency	Expires Today
7	1/17/2022			Emergency	Expired
8	1/19/2022			Urgent	In Progress
9	1/18/2022			Urgent	In Progress
10	1/17/2022			Urgent	In Progress
11	1/16/2022			Urgent	Expires Tomorrow
12	1/15/2022			Urgent	Expires Today
13	1/14/2022			Urgent	Expired
14	1/19/2022			Routine	In Progress
15	12/29/2021			Routine	In Progress
16	12/28/2021			Routine	About to Expire
17	12/24/2021			Routine	About to Expire
18	12/23/2021			Routine	Expires Tomorrow
19	12/22/2021			Routine	Expires Today
20	12/21/2021			Routine	Expired
Sheet943

Cell Formulas
Range	Formula
E5:E20	E5	=IFERROR(LOOKUP(A5-TODAY()+IF(D5="Emergency",1,IF(D5="Urgent",4,28)),IF(D5="Routine",{0,1,2,7},{0,1,2}),IF(D5="Routine",{"Expires Today","Expires Tomorrow","About to Expire","In Progress"},{"Expires Today","Expires Tomorrow","In Progress"})),"Expired")

Thank you jtwak - this is exactly what I need. Very much appreciated. It will go to good use in helping to run maintenance schedules in Care Homes in Northern Ireland and perhaps further afield . Thanks again

 

[jtakw]

Thank you jtwak - this is exactly what I need. Very much appreciated. It will go to good use in helping to run maintenance schedules in Care Homes in Northern Ireland and perhaps further afield . Thanks again

You're welcome, glad we finally figured it out, and it works for you.
And by the way, it's jtakw.