IF statement with a Wildcard not working, workaround?

[Funkymonkey0073]

Hi All

I need help. I need to create a formula that shows 'overdue' if it is more than 10 days past the jobs completion date. The problem I am having is that I wanted to use an IF statement but with a wildcard but now know that is not possible but I don't know a workaround.

I have a large spreadsheet (2000 rows) , in column A is the job ref, in Column G is the completion date and in column K is where i need the formula.

I only need the formula to pick up jobs in column A with the prefix OLP (the refs will be something like OLP1234 but there are many others with different prefix's i.e. FLO1234, BDO1234 etc), the prefix will always be 3 letters.

Hopefully this is enough information but of course if you need more please let me know.

Thanks in advance.

[jeffreybrown]

Maybe...

=IF(AND(LEFT(A2,3)="OLP",G2+10 < TODAY()),"Overdue","")

[Funkymonkey0073]

Works like a charm. Thank you very much for your quick response and expert advise, very much appreciated.

[jeffreybrown]

You are very welcome and thanks for the feedback.

[Funkymonkey0073]

Hi, I've only just realized that this formula displays 'overdue' even in the cell in column G is blank, I would have thought the last part of the formula would just display as blank if the criteria wasnt met but that's not the case and I'm not sure why. Can anyone see the problem?

[Pepe Le Mokko]

Perhaps =IF(AND(LEFT(A2,3)="OLP",G2+10 < TODAY(),G2<>""),"Overdue","")

[Funkymonkey0073]

Unfortunately not, that doesn't display anything at all no matter what date is entered.

[jeffreybrown]

To avoid any more guessing, how about a sample file to work from.

Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[Funkymonkey0073]

Test spreadsheet attached. I've not added a before and after as mentioned as I believe its all self evident in the spreadsheet attached. Of course if its not I'll do a before and after.

[63falcondude]

It looks like all that you have to do is adjust the rows in the formula from post #6.

That is, in L5:

=IF(AND(LEFT(A5,3)="OIL",G5+10 < TODAY(),G5<>""),"Overdue","")

[Funkymonkey0073]

Oh bl**dy hell, I owe you all an apology. My original post was for the prefix 'OLP' but that was changed (in house) and I was just amending the formulas you guys have been giving to look for 'OIL', post 6 had used the original OLP ref and I hadn't noticed, but in my defense there has been a weekend in between! Thank you all, I have slapped my wrist for being so stupid.

[jeffreybrown]

Glad you got it all sorted out and thanks for the feedback.

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