Help with IF(AND formula when two dates are listed

[mcvideo]

Greeting Experts

Need help with my last formula. I have spent time working on this and i may have made this more difficult as I am still a newbie but learning at a good pace. And thanks to great people like you in this group.. link attached to excel saved in my google drive. Hope this is not a issue. Right given to open and edit.

https://drive.google.com/file/d/1_-d...ew?usp=sharing (in link case is not good you can copy and paste from here)

https://drive.google.com/file/d/1_-d...ew?usp=sharing

I have comment to explain how i have my sheet set up and how each cell works off the neighboring cell. I believe i have everything figured out as i have played with the data and formulas seem to behave as they should. Except for ROW 5, cell O5. I need this cell to stay blank if dates are present in column L&M. Cell O5 has formula to give me a aged bucket range when no end date is in col-M. As i add a date in M5 i need cell O5 to remain blank..

I also attached the file for DL.

Any help is appreciated.. Thank you!

[alansidman]

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however the thread title does not really convey what your request is about.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)

[mcvideo]

Alan

Thank you sir for the kind reminder. I made the correction.

[Beamernsw]

Modifying the very first part of your formula with the following should add the functionality you require I think.

Please Login or Register  to view this content.

Full formula is:-

Please Login or Register  to view this content.

[mcvideo]

I will give it a try Beamernsw and report back. Thank you.

Modifying the very first part of your formula with the following should add the functionality you require I think.

Please Login or Register  to view this content.

Full formula is:-

Please Login or Register  to view this content.

[Pepe Le Mokko]

Administrative Note:

Please don't quote entire posts unnecessarily. They clutter threads and make them hard to read.
Use the "Quick reply" instead
Thanks

[mcvideo]

Beamernsw, thank you for your help. Worked great. These long formulas get confusing after a while.

[mcvideo]

Beamersw, maybe you can help me with one last touch. Using the same example spreadsheet uploaded, if i have dates listed on both cells L & M is there a way using another IF formula to stop the NETWORKDAYS from continuing in column P? I would like to keep track of the average days through completion. If I leave it as it is today and dates are in both columns then the count of days continues. I loose track of the aged days through completion. I am tasked to automate this process as much as possible which is why I am asking if this is possible with another IF condition. If so, I would be happy to hear back from you with your thoughts/solution.

[Beamernsw]

I'm sorry, I don't quite understand what it is you are asking exactly. At the moment column P shows TBD unless there is something in both L and M, once both dates are entered it shows the days between those 2 dates. It doesn't keep counting, it just shows how many days between those 2 dates.

Edit:-
Unless you mean the Age Days in column N..... in which case you could try:

Please Login or Register  to view this content.

This takes the minimum (or earliest) date from either "TODAY()" or "Submitted John" and then subtracts L2 from that.