This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(128, 8%, 22%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EA%3C/text%3E%3C/svg%3E)
I currently have the following formula in column AV
=IF(AU2="D/D","",IF(AU2>EDATE(B2,5),"DELAY",""))
AU column contains either "D/D" or current estimated delivery date, B2 contains the order date. The above shows "DELAY" if an order has taken longer than 5 month and nothing if its already been delivered.
What I want to do is add to the above IF statement that also says "DELAY" if the the current status in column AR says "ORDER RECEIVED" and its status date in column AT is more than 3 months old from today's date. I think I need something like the following that works and not give me #value!
=IF(AR324="ORDER RECEIVED",IF(EDATE(AT324,3>TODAY()),"DELAY",""),IF(AU324="D/D","",IF(AU324>EDATE(B324,5),"DELAY","")))
Any help would be appreciated
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.
Answer accepted by question author
Solved it with this
=IF(AU324="",IF(EDATE(AT324,3)<TODAY(),"LATE",""),IF(AU324="D/D","",IF(AU324>EDATE(B324,5),"DELAY","")))
Thanks for everyone's help in pointing me in the right direction
Sorry for not putting it across that clearly - brain's riddled with IF possibilities!
Original formula =IF(AU325="D/D","",IF(AU325>EDATE(B325,5),"DELAY","")) works fine
I've worked out the 2nd bit as a seperate formula =IF(AU324="",IF(EDATE(AT324,3)<TODAY(),"LATE",""),"") which works
Now I just need to combine the 2 formula's into 1 nested IF statement.
Sorry, should have mentioned that if a status is still at "Order Received" then no date has been entered in the column AU, hence why I need to work with the date in column AT
Well, I'm not following you on this but, based on the above statement then that would mean:
...IF(OR(AU324>EDATE(B324,5),...
Would always be TRUE thus returning the "Delay" result provided that AU324 does not contain "D/D".
So, we need to make sure AU isn't empty.
Maybe this:
=IF(AU324="D/D","",IF(OR(AND(AU324<>"",AU324>EDATE(B324,5)),AND(AR324="ORDER RECEIVED",EDATE(AT324,3)>TODAY())),"DELAY",""))
--
Biff
Microsoft Excel MVP
Sorry, should have mentioned that if a status is still at "Order Received" then no date has been entered in the column AU, hence why I need to work with the date in column AT
I currently have the following formula in column AV
=IF(AU2="D/D","",IF(AU2>EDATE(B2,5),"DELAY",""))
AU column contains either "D/D" or current estimated delivery date, B2 contains the order date. The above shows "DELAY" if an order has taken longer than 5 month and nothing if its already been delivered.
What I want to do is add to the above IF statement that also says "DELAY" if the the current status in column AR says "ORDER RECEIVED" and its status date in column AT is more than 3 months old from today's date. I think I need something like the following that works and not give me #value!
=IF(AR324="ORDER RECEIVED",IF(EDATE(AT324,3>TODAY()),"DELAY",""),IF(AU324="D/D","",IF(AU324>EDATE(B324,5),"DELAY","")))
Any help would be appreciated
Try it like this...
=IF(AU324="D/D","",IF(OR(AU324>EDATE(B324,5),AND(AR324="ORDER RECEIVED",EDATE(AT324,3)>TODAY())),"DELAY",""))
--
Biff
Microsoft Excel MVP