Multiple Conditions based on blanks/non-blanks and dates

Hello. I've had so much help with a spreadsheet in the last 24 hours and think I've got stuck at the final hurdle! In our business, when a customer request is logged, a team has a target to close out the request by the end of the following working day.

In the attached sample, the formula in column M is not performing the right operation because it doesn't consider requests that are still open (i.e., blank cell in column J).

I need column M to result in a 'No' if the following conditions apply:

EITHER
- The cell in column L is >2
OR
- The cell in column L is blank AND the date in cell B2 (today) is beyond the end of the day after the date in column I. (For example, if a call was logged 28/09/2021 08:50:00 and is still open, it has missed SLA and should have a 'No' in column M.

Goodness...I hope that makes sense! Can anyone help, please? Promise it's my last question

Hi,
Does this help?

M3=IF(OR(L3>2,AND(ISBLANK(L3),$B$1>I3)),"No","Yes")

This seems to be what you have described:

=IF(OR(L3>2,AND(L3="",$B$1>INT(I3)+1)),"No","Yes")

Originally Posted by clari55a

Thanks for helping me It's not quite right, I don't think. For example, row 4 in the sample shouldn't be a 'No' as we're not yet at the end of the day. It should only become a 'No' on Monday...

When you provide a sample workbook, you should mock up a column with the results you want. Do this manually, please, and reattach your workbook.

Does this get closer (done before looking at your workbook)?

=IF(OR(AND(L3<>"",L3>2),AND(L3="",$B$1>WORKDAY.INTL(INT(I3)+1,1,1))),"No","Yes")

OK - what you are saying and what you are showing do not seem to be completely in sync, so from my point of view, not fully following what you want, I'd suggest you add a column with the deadline date manually calculated.

You also need to clarify WHEN you want a result to show and WHEN you want the cell to remain blank.

It might be worth talking us through the five examples you have given in the workbook.

Can you do as i asked? Provide a copy of the workbook with the following columns added manually:

1. Expected results.
2. The deadline date that needs to be used in the calculation.
3. A brief description of why that row needs the outcome stipulated in point 1.

We are going to go round in circles otherwise, and one of us (probably me!) will end up getting pretty frustrated.

Try this:

=IF(AND(J3="",TODAY() > WORKDAY.INTL(INT(I3)+0.99999,1,1)+0.99999),"No",IF(J3="","",IF(J3 < WORKDAY.INTL(INT(I3)+0.99999,1,1)+0.99999,"Yes","No")))

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.