Enhancing nested IF Formula

[MHamid]

Hello,

I have the following formula to provide the number of the month provided in 3 separate date columns.
Explanation current formula: If there is a confirmation date in cell AC7, then if the month of completion date in cell AE7 matches, the planned completion date in cell AD7, then provide the month of the completion date in cell AE7. If it does not match, then if the completion date month is less than the confirmation date month in cell AC7, then provide the confirmation date month in cell AC7. If there is no confirmation date in cell AC7, then if the account status in cell Z7 equals "Completed", provide the month of the completion date in cell AE7, if not then if the month of the planned delivery date in cell AD7 is blank, then provide the month of the completion date in cell AE7. If not, then provide the month of the planned delivery date in cell AD7.

I need help adding another logic to the formula. I need to add the following as a last piece if all other conditions are not met: If the account is not completed (status cell Z7 or Confirmation date cell AC7) and we are in quarter 2 or 3 or 4, then give me the month of the quarter (4, 7, 10).
Should I add a helper to provide the quarter months (1=Q1, 4=Q2, 7=Q3, and 10=Q4)?

=IF($AC7<>"",IF(MONTH($AE7)=MONTH($AC7),MONTH($AE7),IF(MONTH($AE7)<MONTH($AC7),MONTH($AC7))),IF($Z7="Completed",MONTH($AE7),IF($AD7="",MONTH($AE7),MONTH($AD7))))

Thank you,

 

[jasonb75]

There is nowhere for the logic that you describe to fit into the current formula without removing something. See if this gives the results needed.

=IF($AC7<>"",MONTH(MIN($AC7,$AE7)),IF($Z7="Completed",MONTH($AE7),IF($AD7="",MONTH($AE7),FLOOR(MONTH($AD7)-1,3)+1)))

 

[MHamid]

No, that doesn't do what I need. I need the current results that i am given, plus pushing the accounts not completed by the end of the current quarter to the next quarter (this can be done based on the current week's date in cell P3). For instance, I have 41 accounts that state the planned completion are within Q2 and Q3 and I need these pushed to Q4.
Do I have any other options?

 

[jasonb75]

Please post an example using XL2BB so that we can get a better idea of what is required. Enter your existing formula into one column and type your expected results into the next column where the formula is currently incorrect.