If statement problem.

**jtmoore** · 07-11-2014, 10:05 AM

Hello everyone,

I have a problem.
In my sample workbook you can see the on-time column is set up to Yes or NO
based off whether the due date is earlier than the current date and if "Sent to Customer" appears in the notes column.

I need to be able to put Sent to Customer and the date it was sent in the notes column without messing up the On-time column.

For instance in my sample workbook, I have "Sent to Customer 07-09-14" in the notes column this should be considered on-time.
But I don't know how to tell the On-time column to ignore/accept the date since each date will be different.

Is there a way to do this?
Thanks in advance for any and all assistance!

Sample Workbook.xlsx

**Andy Pope** · 07-11-2014, 10:28 AM

one way

=IF(TODAY()<=[@[Due Date]],"Yes",IF(ISNUMBER(SEARCH("sent to customer",[Notes])),"Yes","No"))

Although you might find it easier to add a NoteDate field and keep the date and note separate. This would also allow you to have a data validation list of Notes is such an approach was appropriate.

**jtmoore** · 07-11-2014, 10:45 AM

The list isn't necessary the only value that is consistently recurring is 'Sent to Customer'.
Your solution works great! I never would of thought of using ISNumber and Search together.

Thanks!