Creating multiple IF Functions with some nested IF functions in false value return

Hi All,

I am trying to put together a largish IF Function with multiple IF functions and some nested IF functions.

I have tried to build each one separately but need them all in one cell. So far i have got these:

• IF(AND(I9>=3,DATEDIF(T4,U9,"d")>=3),"On Track - Blackbook","BlackBook Overdue")
• IF(AND(T4>=M9,T4<=N9),"Clock Stopped",IF(AND(I9>=0,DATEDIF(T9,V9,"d")>=0),"On Track - Blackbook","BlackBook Overdue"))
• IF(AND(T4>=R9,T4<=S9),"Public Comment Period",IF(AND(I9>=0,DATEDIF(T9,V9,"d")>=0),"On Track - Blackbook","BlackBook Overdue"))
• IF(AND(T4>=T9,T4<=V9),"In Blackbook Process",IF(AND(I9>=0,DATEDIF(T9,V9,"d")>=0),"On Track - Blackbook","BlackBook Overdue"))
• IF(AND(T4>=V9,T4<=W9),"Blackbook Complete in Appeal Period",IF(W9=”N/A”),"Complete","Blackbook Complete in Appeal Period"))
• IF(AND(T4>=W9,T4<=Y9),"On Track – Appeals Report",IF(T4>=Y9),"Appeals Report Overdue"))

Im not sure of how to put it all together as one formula.

Any help would be greately appreciated

Jake

Hi Jacc,

Thanks for the reply.

Ive attached a sample document to this post.

I would like the final formula to be in cell 'J9' - if its possible!?

thanks Sample.xlsx

Jake

Hi Jacc and Skywriter,

Thanks for the feedback.

Im looking into both of what you have said/provided and i'll report back soon

Jake

Hi all,

Skywriter thank you for your comments above, they’re very much appreciated, however what Jacc has provided is really quite close to what I was after. If you take a look at what Jacc has posted you might get a better understanding of what im trying to explain compared to me trying to explain it.

To answer questions about what this spreadsheet is for, the best explanation is that it is a bit of a Date predictor of certain upcoming points in a long processes/a process map/a live workload tracker the process.
Ive done some testing of what Jacc provided and it is making excel perform what I was intending in most cases.

The only scenarios that don’t seem to be working are the following:
“On Track – Blackbook” and/or “Blackbook Overdue”
Relative part of Formula:
IF(OR( AND(I9>=3,DATEDIF(T14,U9,"d")>=3), AND(I9>=0,DATEDIF(T9,V9,"d")>=0) ),"On Track - Blackbook","BlackBook Overdue"))))))))

“Appeals Report Overdue”
Relative part of Formula:
IF(T14>=Y9,"Appeals Report Overdue",

Another point that I didn’t think of and the formula is returning error “#VALUE!” is when column ‘R’ has “Enter Start Date” and column ‘S’ is blank. They will always end up with dates being manually inserted but until they do they will remain as “Enter Start Date” and a blank cell. With this though then I actually need a new return value of “Not Yet Advertised".
So my guess would be to try and include something like:
IF(AND(R9=”Enter Start Date”, S9=””),"Not Yet Advertised",

Ive attached another sample that’s incorporated your work Jacc and have added in more rows of info so that each row’s status in column ‘J’ is returning a different part of the formula to get an easier picture of whats going on. Ive also add in little comment boxes for each cell down column 'J' with some notes about what i think is going on for each one.

Any feedback would be much appreciated!

Jake