Formula that is based on date and string

[olga6542]

Hi
I have a field called "Project Reporting", which should simply be "Yes" or "No". However, the result depends on the following:
It should be Yes if:
WIP field has a "Yes" string OR
WIP field has "No" string and Completion Date is within the 2 months from the current date.
ELSE:
Project Reporting should be indicated as "No".

[jeffreybrown]

Maybe

=IF(AND(B2="Yes",A2>=DATE(YEAR(TODAY()),MONTH(TODAY())-2,DAY(TODAY()))),"Yes","No")

[olga6542]

No, It does not satisfy the intent.
It should be Yes if:
WIP field has a "Yes" string OR WIP field has "No" string and Completion Date is within the 2 months from the current date

Using your formula, it says No throughout. C2 should be Yes, C3 should be No, C4 should be Yes and C5 should be Yes.

[jeffreybrown]

C4 does not have a date so how is it "Yes"

Try...

=IF(AND(OR(B2={"Yes","No"}),A2>=DATE(YEAR(TODAY()),MONTH(TODAY())-2,DAY(TODAY()))),"Yes","No")

[olga6542]

It's not purely based on the date. If there is no Completion Date, it means that the work is not completed and therefore WIP=Yes. It should be based on the WIP entry as well as completion date entry. WIP should be a primary factor and completion date should be secondary. Don't use the assumption if the date is blank, WIP=Yes.

[jeffreybrown]

=IF(ISBLANK(A2),"Yes",IF(AND(OR(B2={"Yes","No"}),A2>=DATE(YEAR(TODAY()),MONTH(TODAY())-2,DAY(TODAY()))),"Yes","No"))

[olga6542]

It works but it does not meet the intent. It's important to use WIP as a primary factor because project can be cancelled and might not have a completion date. In such cases, WIP=No. Currently, I have the following formula for WIP: =IF(OR(U10<>"",COUNT(SEARCH({"cancel"},W10))),"No","Yes"). If you use the logic provided and I change B2 to No, Project Reporting still equals to Yes. WIP is the prime criteria, completion date is a secondary

[FDibbins]

see if this will give you what you need? it gives all the answers you specified

=IF(OR(B2="Yes",A2="",A2>=TODAY()-60),"Yes","No")