HELP WITH NESTED STATEMENTS

[dougie]

HELP.....

I have a query that requires FOUR possible answers depending on the data contained within a cell.


I have FOUR columns. The STATUS column being where i want the formula to be.

DATE ACCEPTED, DATE AUTHORISED, DATE RECEIVED, STATUS.

What I am trying to achieve is:

1: If DATE AUTHORISED cell is NULL after five days from DATE ACCEPTED cell, then give me a message to say "Chase Authorising Officer" once date entered into DATE AUTHORISED cell say "AUTHORISED"

Then,

2: Once DATE AUTHORISED has been entered and DATE RECEIVED cell is NULL after 5 days give a message to say "CHASE ME" once date entered into DATE RECEIVED cell say "COMPLETE".

I dont know if it can be done, but I will be extremely gratefull for the time giving in solving this nightmare for me.

Many thanks in anticipation.

Dougie
[/img][/url][/list][/code]

[onlyadrafter]

Hello,

I haven't tested this thoroughly, but it appears to work, I think.

=IF(C2<>"","COMPLETE",IF(AND(B2="",TODAY()-5>A2),"CHASE OFFICER",IF(AND(C2="",TODAY()-5>B2),"CHASE ME","AUTHORISED")))


Where the dates are in A2, B2 and C2.

Let me know if any problems/errors.

[Zack Barresse]

how about...

=IF(C2="",IF(TODAY()>A2+5,"Chase Authorizing Officer",IF(TODAY()>B2+5,"Chase Me","AUTHORISED")),"Complete")

assuming your dates are in a2:c2 respectively.

edit: sorry drafter, didn't see ya, evidently i didn't read his earlier post here either.

[fairwinds]

Hi,

Maybe this is an unnescesary post but I think this is how I would have wanted it to look.

=IF(B2="",IF(TODAY()-A2>5,"Chase Authorising Officer",""),"Authorised")&IF(B2="","",IF(C2="",IF(TODAY()-B2>5," / Chase Me","")," / Complete"))

[Ekim]

Fairwinds,

Nice formula. I would amend it slightly to show a blank in the Status column if there is no date in column A:

=IF(A2="","",IF(B2="",IF(TODAY()-A2>5,"Chase Authorising Officer",""),"Authorised")&IF(B2="","",IF(C2="",IF(TODAY()-B2>5," / Chase Me","")," / Complete")))

Also, the OP may wish to trap certain typos errors e.g. the authorization date must be greater than, or at least the same as, the acceptance date. Ditto for the received date compared to the authorization date. At the moment, any date in the Date Authorized column or the Date Received column will trigger a message in the Status column.

In cell B2:
Data menu | Validation
Allow: Date
Data: Greater than or Equal to
Start date: A2
Error Alert: “The Date Authorized must be greater than, or the same as, the Date Accepted”


Regards,

Mike

[dougie]

Many thanks to you all.

Fairwinds, thanks but there will always be data in column a.

OfF to test now.

cHEERS