# IF with multiple Date conditions

#### well

##### Board Regular
Can someone correct my formula or alternative formula that will solve my problem. This will also help me to learn how to do a multiple conditions by Dates in the future.

Here are my conditions. "J4" is the column where they input the date:
IF J4 = blank (no entry), return blank
IF J4 = Date today, return "Enter in VIPER"
IF J4 - Date today <=6, return "Due Soon"
IF J4 - Date today >=7, return "Due Now"
IF J4 - Date today >=30, return "Completed"

this is the formula I attempted to make but it didn't work:
IF(J4="","",IF((TODAY()=J4),"Enter in VIPER",IF(AND(J4-TODAY()<=6),"Due Soon",
(IF(((J4-TODAY())>=7),"Due Now",(IF(((J4-TODAY())>=7),"Completed"))))).

### Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
Check whether the below formula meets your purpose :

=IF(J4="","",IF(TODAY()=J4,"Enter in VIPER",IF(DAY(J4)-DAY(TODAY())<=6,"Due Soon",
IF(DAY(J4)-DAY(TODAY())>=7,"Due Now",IF(DAY(J4)-DAY(TODAY())>=7,"Completed")))))

for the portion for "completed" is not working "IF(DAY(J4)-DAY(TODAY())>=10,"Completed"
just need to correct instead of 7 it supposed to 10 "IF(DAY(J4)-DAY(TODAY())>=10,"Completed", my mistake
Check whether the below formula meets your purpose :

=IF(J4="","",IF(TODAY()=J4,"Enter in VIPER",IF(DAY(J4)-DAY(TODAY())<=6,"Due Soon",
IF(DAY(J4)-DAY(TODAY())>=7,"Due Now",IF(DAY(J4)-DAY(TODAY())>=7,"Completed")))))

Think this is what you're after...

=IF(J4,LOOKUP(J4-TODAY(),{0,6,10,30},{"Enter in VIPER","Due Soon","Due Now","Completed"}),"")

can you be able to fix a little bit for "Enter in VIPER", I want if "0" or same date as J4 is "Enter in VIPER" only, if more than zero or if within 1 to 6 days is "Due Soon".
interesting formula of yours different approach.
Think this is what you're after...

=IF(J4,LOOKUP(J4-TODAY(),{0,6,10,30},{"Enter in VIPER","Due Soon","Due Now","Completed"}),"")

can you be able to fix a little bit for "Enter in VIPER", I want if "0" or same date as J4 is "Enter in VIPER" only, if more than zero or if within 1 to 6 days is "Due Soon".
interesting formula of yours different approach.

Slight tweak...
=IF(J4,LOOKUP(J4-TODAY(),{0,1,6,10},{"Enter in VIPER","Due Soon","Due Now","Completed"}),"")

Slight tweak...
=IF(J4,LOOKUP(J4-TODAY(),{0,1,6,10},{"Enter in VIPER","Due Soon","Due Now","Completed"}),"")

Perfect Neil. Interesting approach and it's simple. I learned something new. Appreciated!

Thank on both of you Neil and Sanjeev,
Well

Slight tweak...
=IF(J4,LOOKUP(J4-TODAY(),{0,1,6,10},{"Enter in VIPER","Due Soon","Due Now","Completed"}),"")

Perfect Neil. Interesting approach and it's simple. I learned something new. Appreciated!

Thank on both of you Neil and Sanjeev,
Well

Replies
6
Views
569
Replies
23
Views
226
Replies
8
Views
82
Replies
1
Views
596
Replies
1
Views
400

1,203,462
Messages
6,055,565
Members
444,799
Latest member
CraigCrowhurst

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