I need to search in a column for the word "complete" and if it doesn't exist then I need to subtract a date in another column from today (TODAY()). I have several fields that do not have any values so I'm also doing a check for NULL fields. This is my formula:
IF(AND(SEARCH(M30,"complete"),O30<>""),"*",O30-TODAY())
(Column M is string of text and column O is a date.)
When I run this formula I'm getting a #VALUE error so I assume something needs to be typecast.
I don't think it will matter for this particular question but I'm using Excel 2013.
When the search term is not found the formula will produce an error. So you have to check for an instance of an error to see if it's found or not.
This formula should work:
=IF(AND(NOT(ISERROR(SEARCH(M30,"complete"))),O30<>""),"*",O30-TODAY())
N.B. - Bear in mind that the Search function as you wrote it may actually be reversed. Depending on your needs, you may need to write it as SEARCH("complete",M30)