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 ?????

 

[jtakw]

Hi,

See if this might work for you:

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

Cell Formulas
Range	Formula
E5:E13	E5	=IFERROR(LOOKUP(A5-TODAY(),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]

Can you post some sample data along with expected results.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Hi again. The data you provided is along the lines of what I would like - My post at 5.32 pm is what I would like it to do. I didnt explain it accurately in my first post; sorry. I dont have any sample data to post.

Can you post some sample data along with expected results.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Hello again. I dont have any data to post. Will this explain more fully what I would like to achieve please

 

[Orangeanorak]

Hi again. The data you provided is along the lines of what I would like - My post at 5.32 pm is what I would like it to do. I didnt explain it accurately in my first post; sorry. I dont have any sample data to post.

Hello again. I dont have any data to post. Will this explain more fully what I would like to achieve please

 

[jtakw]

Did you try my formula in Post #11 ?

 

[Orangeanorak]

Did you try my formula in Post #11 ?

I'm not sure what to do with it - do I have to include your book3 data somewhere. Sorry I am inexperienced

 

[jtakw]

No, Based on your original image in your 1st post (Post # 1), just copy and paste my formula in E5 and drag down.

 

[Orangeanorak]

No, Based on your original image in your 1st post (Post # 1), just copy and paste my formula in E5 and drag down.

Thank you for this jtakw - your time is very much appreciated. Unfortunately I managed to confuse myself with the first post. What I wish to have is in my last post. Grateful for your time

 

[jtakw]

Ok, see if this modified version works for you, assuming your Dates are in Column A, Status in Column D, Deadline in Column E, all starting at Row 5, just copy and paste my formula in E5 and drag down:

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

Cell Formulas
Range	Formula
E5:E15	E5	=IFERROR(LOOKUP(A5-TODAY()+IF(D5="Emergency",1,IF(D5="Urgent",4,0)),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]

No, Based on your original image in your 1st post (Post # 1), just copy and paste my formula in E5 and drag down.

Thank you - I will look at this tomorrow

 

[jtakw]

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