Search criteria in a field to evaluate if an event happened within 30 days pre and post

I /think/ this is what you want.. I've used a cpl IFs and a couple COUNTIFSs to at least tell you if the criteria is met.. the formula looks like this:

(I used a table so the formula is also table references)

=IF(LEFT([@[CPT Description]],2) = "Of",
IF(
AND((COUNTIFS([MRN],[@MRN],[SERVICE DATE],">"&[@[SERVICE DATE]]-30,[SERVICE DATE],"<"&[@[SERVICE DATE]],[CPT Description],"=Administered Chemo")),
(COUNTIFS([MRN],[@MRN],[SERVICE DATE],"<"&[@[SERVICE DATE]]+30,[SERVICE DATE],">"&[@[SERVICE DATE]],[CPT Description],"=Administered Chemo"))),
TRUE,FALSE),
"")

It works something like.. if the description is an office visit - ie it starts with the letters 'Of' - then carry on, else leave the cell blank. I did this as from your description it sounded like you were only interested in pre/post office visits so I thought this was neater. Just be aware that this formula is looking for the CPT description to either start 'Office' or be 'Administered Chemo'.

If it is a visit then we are into another IF. This time we check to see if both of our COUNTIFS statements are larger than 0. The first COUNTIFS checks that MRN is the same as the row we are interested in, then that the service date is greater than the visit date - 30, then it checks that it is less than the visit date and finally that the description is 'Administered Chemo'. If this counts anything other than 0, we must have at least one Chemo appointment that meets the criteria.

Then the second COUNTIFS does the same thing but this time for service date is less than + 30 days.

If both conditions are meet then it returns True and your criteria are met, if it returns False then one or other criteria were not met.

I'm attaching my copy of the spreadsheet I was working on. I've added some extra columns that you can just delete as you see fit. They are:

'30 days before' - This is just the COUNTIFS for the before date on its own to make it easier to understand

'30 days after' - same as above but for after

'Indicator' - the combined formula as described above

'Indicator 2' - an alternative version of the formula that doesn't check the type of CPT. ie in this COUNTIFS we no longer care if it was an office visit or a chemo visit, as long as they were seen within the pre/post 30day criteria. (i wasn't sure which was right)

On your example you have a Y next to the 17/7/18 visit, but unless I'm being a bit daft, and none of the above works, that should be false. There isn't a chemo session within 30days either before or after.

Hopefully that works for you.