IF(OR(AND( TO USE WITH DATE

jevi

Active Member
Joined
Apr 13, 2010
Messages
339
Office Version
  1. 2016
Platform
  1. Windows
Hi All,

I have a strange database and I need to check some documents which have a due date. So I need to have Alerts were the two dates are 10 days before the today date.

The alerts are always confronting the Date to Resign and the Date Due To with the today() = 22/09/2021. So the first Alert is based on the Date to Resig, while ALERT 2 it should be based on Date Due to and Alert 1. So I need the result as in column Alert 2 and the reason is:

First row: If in the cell of column Alert 1 is YES automatically in the column of Alert 2 it has to be NO or " ", it doesn't matter as I already have a Alert 1.
Second row: it is in both columns NO because the dates in both columns are not today date +10 days that I Need an Alert, but are far away from 22/09/2021 so not interested.
Third row: the same situation as in row two....I can't do nothing about thouse documents as they have alreay expired.
fourth row: I need a Yes in the Alert 2 column because there is a NO in Alert 1 Column and the Date Due To 01/10/2021 is close to today date (22/09/2021) + 10 days so I need to have the alert because I need to know it 10 days in advance before the date due to 01/10/2021 is in order to renew the document.

Maybe I was not so clear :(...still let me know if you need more information.

Thank you

1632329483228.png
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Try

Book7
ABCDE
1Date to ResignDate Due ToAlert 1Alert 2
210/2/202112/31/2021YesNo
35/2/20217/1/2021NoNo
412/3/20203/3/2021NoNo
5No Resign10/1/2021 Yes
6
Sheet1
Cell Formulas
RangeFormula
C2:C5C2=IFERROR(IF(AND(A2-TODAY()<=20,A2-TODAY()>0),"Yes","No"),"")
D2:D5D2=IFERROR(IF(C2="Yes","No",IF(AND(B2-TODAY()<=20,AND(B2-TODAY()>0)),"Yes","No")),"")
 
Upvote 0
Solution
Another couple of formulas
Dante Amor
ABCD
1Date to ResignDate Due ToAlert 1Alert 2
202/10/202131/10/2021YESNO
302/05/202101/07/2021NONO
403/12/202003/03/2021NONO
5No Resign01/10/2021NOYES
Hoja3
Cell Formulas
RangeFormula
C2:C5C2=IFERROR(IF(AND(A2>TODAY(),A2-10<=TODAY()),"YES","NO"),"NO")
D2:D5D2=IF(C2="YES","NO",IFERROR(IF(AND(B2>TODAY(),B2-10<=TODAY()),"YES","NO"),"NO"))
 
Upvote 0
Thank you so much Sufiyan....it worked as a treat with the result I wanted:).

@ Dante the formula in C2 worked great but the one in D2 is not giving the result I wanted. I will check it tomorrow for curiosity...still thank you:)
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,202
Members
448,554
Latest member
Gleisner2

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