IF and AND function

[Mambaout626]

Hi all,

Seek the help from excel guru urgently.

In the attached spreadsheet, I use it to track if deadline is over due or not.

Column 1 will be today's date and column O3,P3 and Q3 will be my parameters.

Scenario as per below:

1). If A/C exited (O3) = Yes, then Status of Exit (Q3) = blank.

2). If A/C exited (O3) = No, AND Expected date of exit (P3) smaller then today's date, then Status of Exit = "Follow up"

3). If A/C exited (O3) = No, AND Expected date of exit (P3) bigger then today's date, then Status of Exit = "Pending"

**4).IF If A/C exited (O3) = BLANK, then Status of Exit = "Follow up"

Please assist with the query.

[JohnTopley]

Try

=IF(O4="","Follow up",IF(O4="Yes","",IF(O4="No",IF(P4 < TODAY(),"Follow up","Pending"))))

What if P4 = TODAY() ????

[Mambaout626]

Thanks John, work like a charm.

if P4 = TODAY(), can I have a msg "due today"?

[JohnTopley]

Try


=IF(O4="","Follow up",IF(O4="Yes","",IF(O4="No",IF(P4 < TODAY(),"Follow up",IF(P4=TODAY(),"Due Today","Pending")))))

[Mambaout626]

Sorry John,

Why is it when I put N/A in Column O4, Q4 = "FALSE"?

Can I have O4 = N/A, then Q4 = "A/C Retain"?

[JohnTopley]

Try

=IF(O4="N/A","A/C Retain",IF(O4="","Follow up",IF(O4="Yes","",IF(O4="No",IF(P4 < TODAY(),"Follow up",IF(P4=TODAY(),"Due Today","Pending"))))))

[Mambaout626]

I've changed the spreadsheet format and requires following assistance

Column 1 will be today's date and column D3, F3,G3, H3 and I3 will be my parameters.

Scenario as per below:

1). If A/C Strategy (D3) = "Exit" or "Retain", then Status (H3) = blank and "Date of 1st reminder" (G3) will be 10 business days after "Date of Form sent to Biz" (F3) and "Next Deadline" (I3) = 10 business days after F3.

2). If A/C Strategy (D3) = "Pending", then Status (H3) = "Pending" if today's date is smaller than
"Date of Form sent to Biz" (F3), and "Date of 1st reminder" (G3) will be 10 business days after "Date of Form sent to Biz" (F3) and "Next Deadline" (I3) = 10 business days after F3.
nd if Date of Form sent to Biz" (F3) > 30 business day, "Report to manager"

3). If A/C Strategy (D3) = "Pending", then Status (H3) = "Follow up " if today's date is bigger than
"Date of Form sent to Biz" (F3), and "Date of 1st reminder" (G3) will be 10 business days after "Date of Form sent to Biz" (F3) and "Next Deadline" (I3) = 10 business days after F3.
and if Date of Form sent to Biz" (F3) > 30 business day, "Report to manager"


Please assist with the query.

[JohnTopley]

If I read your requirements correctly, the results for G3 and I3 are the same i.e independent of D3.

Where does "Report to Manager" appear?