Forum Discussion
searching for dates to trigger an action.
Unsurprisingly, the best solution depends on the tools available. Given access to regular expressions (here I use Charles Williams's Fast Excel) the first 4-digit string, giving the year, may be extracted for each row and presented as a conditional format icon.
Alternatively, the condition may be used in a FILTER formula.
Avoiding the use of add-ins one could then have
- Peter, Thank you for this information. These formulas look interesting but I do not know enough to know where to enter them. Could you elaborate more on the basics of how to enter these and were to enter these? Thanks John Hicks
Hi John
Not so mysterious really; they are 'ordinary' worksheet dynamic array formulas. Ordinary for me that is, others may not agree. The starting point is the defined name 'entry' that I applied to your data entry column, containing a list of dates and addresses. The function rgx.MID comes from a paid add-in so is shown for information but is of little relevance unless you have access to the add-in.
The formula contained in cell C3 generates 1 or 0 as a dynamic array depending on whether the date lies within the specified period or not. This is taken a step further in cell F3 when the function FILTER is applied to remove entries that do not lie within the specified period.
