Formula with many conditions required please

Orangeanorak

Active Member
Joined
Nov 22, 2002
Messages
276
Office Version
  1. 365
Platform
  1. Windows
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 ?????
 

Attachments

  • Screenshot 2022-01-18 15.35.58.png
    Screenshot 2022-01-18 15.35.58.png
    34.8 KB · Views: 21

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
You can do that like
Excel Formula:
=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.
 
Upvote 0
=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.
 
Upvote 0
In that case you need to explain what you want slightly better.
 
Upvote 0
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.
 
Upvote 0
I hope that explains things a bit better.
Not really, but based on your original post, maybe
Excel Formula:
=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")))))
 
Upvote 0
=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
 
Upvote 0
Sorry - I cant get it to work - its above my level of understanding but thank you for your time
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,559
Messages
6,114,302
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top