# Formula with many conditions required please

#### Orangeanorak

##### Active Member
Grateful if a formula can be supplied for this one please. No VBA if possible
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
34.8 KB · Views: 21

### Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

#### Fluff

##### MrExcel MVP, Moderator
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.

#### Orangeanorak

##### Active Member
=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

##### MrExcel MVP, Moderator
In that case you need to explain what you want slightly better.

#### Orangeanorak

##### Active Member
In that case you need to explain what you want slightly better.
Thank you for your patience - I'll do a rethink.

#### Orangeanorak

##### Active Member
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

##### MrExcel MVP, Moderator
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")))))``

#### Orangeanorak

##### Active Member
=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

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

#### Fluff

##### MrExcel MVP, Moderator
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.

Replies
1
Views
320
Replies
20
Views
556
Replies
1
Views
62
Replies
3
Views
902
Replies
2
Views
392

1,190,559
Messages
5,981,691
Members
439,730
Latest member
gjvv

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

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