So the title may not make much sense..Sorry about that. Also, I am unable to attach a sample work book so I'm going to try and give as many details as possible.
I have a field titled 'Order Notes'. These order notes are to be updated by the employees with the goal of every note having been updated with a date and note no greater than a week old. The employees place a date somewhere within the note as 11-18; 11/18; 11/18/16; 11/18/2016; 11,18; 11.18; 11.18.; 11.18.16. There are so many typos and variations it's ridiculous. The person who was previously running this report in Excel was individually looking at each note and deciding if the note was greater than a week old or within the one week range. There are hundreds of order notes to look at and takes some time. My job is to automate this report so this person can focus on other tasks. I decided that the CONTAINS function would probably work best and tried something like this: CONTAINS([Order Notes],DATEADD('week',-1,TODAY())) , but as I imagined the string and date combo wasn't working together and I wasn't 100% sure the DATEADD function was the right choice either.
Here are some mock examples of what I am working with:
11/13 We are in need of set up docs, staff reviewed.
11/17-Staff Reviewed:Order Approved
11/18/16 Secondary insurance will need to be done and we need the return ticket.
11,6 staff reviewed and requested set up docs // 10/22 Setup docs needed. Pt.'s approved. Emailed POC.
11.12 We are in need of set up docs, staff reviewed. emailed POC
11.13.- Received dated Rx
11.6.16 Order has been staff reviewed and that we still need set up docs // 10-30 We are in need of set up docs, pt is approved for set up
Insurance verified On 09/29/2016 // 10/17 We are needing a signed SS. Emailed POC.
What I'm hoping to determine is:
- If the order notes contain a date greater than a week old from today then place it in a bin titled "Old Note"
- If the order notes contain a date less than a week old from today then place it in a bin titled "Noted"
-else titled "Blank"
Does anyone know of way I can accomplish what I'm wanting? Any help would be much appreciated!!