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

 

[Fluff]

You can do that like

=IF(D5="Emergency",IF(A5<=TODAY(),"In Progress",IF(A5=TODAY()+1,"Expires Today","Expired")),IF(D5="Urgent",IF(A5<TODAY()+3,"In Progress",IF(A5=TODAY()+3,"About to Expire",IF(A5=TODAY()+4,"Expires Today","Expired")))))

you just need to add the final part in the same manner.

 

[Orangeanorak]

=IF(D5="Emergency",IF(A5<=TODAY(),"In Progress",IF(A5=TODAY()+1,"Expires Today","Expired")),IF(D5="Urgent",IF(A5<TODAY()+3,"In Progress",IF(A5=TODAY()+3,"About to Expire",IF(A5=TODAY()+4,"Expires Today","Expired")))))

Thank you for this Fluff - unfortunately its not working for me. When I paste this formula in to E5 it shows "In Progress" when I expect it to say "Expired." Am I doing something wrong.

 

[Fluff]

In that case you need to explain what you want slightly better.

 

[Orangeanorak]

In that case you need to explain what you want slightly better.

Thank you for your patience - I'll do a rethink.

 

[Orangeanorak]

Sorry Fluff - I confused myself

I wish to enter the date that repair jobs are notified to me in Col A
I flag them in Col D as Emergency, Urgent or Routine
Emergency jobs have to be done within the next day
Urgent jobs have to be done within 4 days
Routine jobs have to be done within 28 days

I request a formula to show the following text in Col E - Deadline please
Emergency jobs - on entry shows: "Expires Tomorrow. the next day "Expires Today", and the next day "Expired"
Urgent jobs - on entry and the second day shows "In Progress" on day 3 shows "Expires Tomorrow" 0n day 4 shows "Expires Today" and from day 5 shows "Expired"
Routine jobs - on entry and up to 21st day shows "In Progress" on days 22 to 26 shows "About to Expire", day 27 "Expires Tomorrow" on day 28 shows "Expires Today" and from day 29 shows "Expired"

I hope that explains things a bit better. Thank you for your help.

 

[Fluff]

I hope that explains things a bit better.

Not really, but based on your original post, maybe

=IF(D5="Emergency",IF(A5<TODAY(),"Expired",IF(A5=TODAY(),"Expires Today","In Progress")),IF(D5="Urgent",IF(A5<TODAY(),"Expired",IF(A5=TODAY(),"Expires Today",IF(A5=TODAY()+1,"About to Expire","In Progress")))))

 

[Orangeanorak]

=IF(D5="Emergency",IF(A5<TODAY(),"Expired",IF(A5=TODAY(),"Expires Today","In Progress")),IF(D5="Urgent",IF(A5<TODAY(),"Expired",IF(A5=TODAY(),"Expires Today",IF(A5=TODAY()+1,"About to Expire","In Progress")))))

Thank you - I'm out --- I'll try later

 

[Orangeanorak]

Sorry - I cant get it to work - its above my level of understanding but thank you for your time

 

[Fluff]

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.